Vlookup Multiple search criterias

**Rage** · 05-20-2010, 10:24 AM

Hi all,

I have this formula that does the following:

=IF($B$4="";"";VLOOKUP($B$4;Bookings!$B$10:$AD$942;4;0))

B4 = "a date"
Bookings!B10:AD942 = The matrix contains dates / names / etc.
4 = Returns the row 4 which is a "name"

How can i make this formula to search for a date (B4) and also include another search criteria like (E4) ( Doesnt matter if its a number or text )

So Vlookup meeting 2 criterias and returning the values based on the IF (B4)

Thanks

**NBVC** · 05-20-2010, 10:35 AM

I don't get it.

Are you looking also to match the value of E4 in another column of the Bookings table?

**Rage** · 05-20-2010, 10:57 AM

I have dates that are the same on the MATRIX TABLE.
If i keep using the formula i posted, it will only retrive the value of the first matching date, thats why i need to had another search criteria, based on E4 value

I hope i made some sense, if not i will post the xls once im able to.

Cheers

**NBVC** · 05-20-2010, 11:10 AM

So are you saying that if doesn't find the B4 value in the first column of the table, then look for the E4 value in the first column of the lookup table?

if not, then you'd better post a sample...

**Chemistification** · 05-20-2010, 11:24 AM

Not sure if this is what your after and its proabably not the prettiest but i sort of get round it by concatonating two columns together to create a unique lookup value. You could concatonate 3 or more if you needed in order to make it unique. Then do a vlookup of this unique lookup value. See the spreadsheet ive attached... hop this is what your after and helps

Chemist

**Rage** · 05-24-2010, 05:55 AM

Originally Posted by Chemistification

Not sure if this is what your after and its proabably not the prettiest but i sort of get round it by concatonating two columns together to create a unique lookup value. You could concatonate 3 or more if you needed in order to make it unique. Then do a vlookup of this unique lookup value. See the spreadsheet ive attached... hop this is what your after and helps

Chemist

Ok i tried to use that forumla

=IF($L$5="";"";VLOOKUP($L$5;Bookings!$B$10:$AE$936;4;0))

Were L5 = the concatenate value

But i get N/A shown as result

PS: I have both values concatenated in both sheets like your example.
Do i need to have the concatenated value in the first column like your example ?

**Rage** · 05-24-2010, 07:58 AM

I managed to do it with another formula.

=IF(ROWS($A$2:$A2)>$H$12;"";INDEX(Bookings!E:E;MATCH(ROWS($A$2:$A2);'Base Dados'!D:D;0)))

Since i was using this formula to get a different criteria, i used this to get what i needed.

Anyway thanks for all the help.

**Rukky** · 11-30-2010, 11:49 PM

Dear All,

Please help me.

I have a kind of data (of about 1000 rows)...in which many employee has many duplicate values.
So i want to do is to bring a column of unique employee and a columns of unique values correcspondence to that particular employee....

Em....if Joy - 10
Tom - 20
Joy - 35
Karl - 56
Tom - 60
Joy - 47
Jim - 98

I want data to be like this...

if i enter "Jon" -data come next cell -10, next cell - 35, next cell - 47
if i enter 'Tom" data comes "20" and "60"

I hope you understand.
Please help me from this..

Thanks/Regards
Neeraj

**NBVC** · 12-01-2010, 12:04 AM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.