PDA

View Full Version : Solved: VLOOKUP multiple criteria



white_flag
02-17-2011, 07:37 AM
Hello

I need your help:
I have an table with 10 columns
col1 col2 ............. col10
12.....30.............. 25
12.....40.............. 85
12.....50.............. 77
12.....60.............. 25
20.....60.............. 56
26.....60.............. 71

I like to know if it is possible, that excel, can do this via VLOOKUP
searching an value from the first col1 then to search on the col2 and then, to give the value from col10 based on condition1 AND condition2

this is possible?

Bob Phillips
02-17-2011, 09:17 AM
Try this array formula

=INDEX(J2:J1000,MATCH(1,(A2:A1000="value1")*(B2:B1000="value2"),0))

white_flag
02-17-2011, 12:27 PM
Bob, thx for the help..but It is not going.

match become Match(1,0-false;1-true,0)...and rezult 1, but not the position of the row were the value is.. any idea why?

Bob Phillips
02-17-2011, 01:59 PM
Shouldn't be the row, because you are matching the first 1 against the array of results.

You must ARRAY enter it.

white_flag
02-17-2011, 02:40 PM
I still don't get it

Bob Phillips
02-17-2011, 03:16 PM
The Match function will return the index of the matching conditions, which indexes into the data range.

Does it work?

white_flag
02-17-2011, 03:48 PM
maybe it is working, but not in my case

Bob Phillips
02-17-2011, 05:42 PM
Hey! I array-entered it, and it magically came up with the answer - 50.

white_flag
02-18-2011, 12:51 AM
I am so lost.

white_flag
02-18-2011, 01:23 AM
array-entered ..those are the magic words :))

Bob , thank you very much

Bob Phillips
02-18-2011, 02:06 AM
Glad you sorted it mate!