Vlookup or index/match with multiple column and criteria?

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?

Hi xirx,

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

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

Regard,
KL


"xirx" <[email protected]> wrote in message
news:[email protected]...
> 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?

Try...

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

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

Hope this helps!

In article <[email protected]>,
xirx <[email protected]> wrote:

> 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?