Vlookup or index/match with multiple column and criteria?

X

xirx

Is it possible to ckeck multiple columns in a table lookup.

E.g: Find the value in column C for which values
in colums A and B are equal to 1 (yields "c")

A B C
0 1 a
1 0 b
1 1 x

The only solution I see is to use a helper column to
concatenate A and B and to a match on the concatenated
search keys...

Is there a solution without helper cells?
 
K

KL

Hi xirx,

try this array formula (ctrl+shift+enter):

=INDEX(C1:C10,MATCH(E1&F1,A1:A10&B1:B10,0))

Regard,
KL
 
D

Domenic

Try...

=INDEX(C1:C3,MATCH(1,(A1:A3=1)*(B1:B3=1),0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

lookup and return column number 2
VLOOKUP formula? 12
Index match with multiple criteria(different than usual) 3
MATCH OR VLOOKUP 3
Multiple criteria VLOOKUP 2
Index match with multiple criteria(different than usual) 0
Macro for comparing columns and highlighting matching/differences 13
INDEX/MATCH -> having issues getting to grips with it 4

Top