Question on excel database search Closed

Question

Hello, 

I'm familiar with the vlookup function in excel.  However, it only allows you to compare one column in a table and return value from another column of the table.  Is there anyway to do the following:

Table A has column 1, 2, 3.  Table B has column 4, 5.  Return the value of column 3 only if both column 1 matches column 4 and column 2 matches column 5 in that particular row?

Many thanks.

Configuration: Windows 7 / Safari 534.3

rizvisa1 · Answer

The most easy way would be if you can add a formula based column in your table A as

=A1 & "|" & B1 (lets say its in G1 cell)

then when you do a look up you can have

=IF(ISERROR(MATCH(D1 & "|" & E1, G:G,0)),"", INDIRECT("C" & MATCH(D1 & "|" & E1, G:G,0)))

DISCLAIMER: I have not tested it. 
I am sure you would be able to make it work, in case there is an error

cblack · Answer

you can nest the vlookup such as:=IF(VLOOKUP(ItemtoLookfor,threetable,2,FALSE)=VLOOKUP(ItemtoLookfor,twotable,2,FALSE),VLOOKUP(B2,threetable,3,FALSE),"n/abbb")

this is in cell b5 

								
Item to lookup	aa							
lookup in 3 col table	10							
lookup in 2 col table	10							
If items agree, then look up	11 << cell b5							



table 3 col
		1	2	3				
		aa	10	11				
		b	20	13				
		c	30	15				
		d	40	17				
								
								


table 3 col 							4	5
							aa	10
							b	25
							c	30
							d	45

Question on excel database search

2 responses

Similar discussions

Subscribe To Our Newsletter!