How do I Double Conditional vlookup for 3 columns?

E

engilo

I need to look up values under two conditions. For example, I have 3 collumns

1 a hi
1 b yo
2 a um

I need to look up "1" in the first column, "b" in the second column, and
return "yo" from the fourth column. Can you do this using some type of
nested vlookups? I am trying to avoid writing the visual basic code for it.
 
D

Domenic

Try...

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

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

Hope this helps!
 
R

RagDyer

I assume when you said to return "yo" from the *4th* column, you really
meant the 3rd column (Column C).

This *array* formula will return the *first* match in Column C:

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

Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead
oft the regular <Enter>, which will *automatically* enclose the formula in
curly brackets, which *cannot* be done manually.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

engilo said:
I need to look up values under two conditions. For example, I have 3 collumns

1 a hi
1 b yo
2 a um

I need to look up "1" in the first column, "b" in the second column, and
return "yo" from the fourth column. Can you do this using some type of
nested vlookups? I am trying to avoid writing the visual basic code for
Click to expand...
it.
 
E

engilo

thank you!

Domenic said:
Try...

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

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

Hope this helps!
Click to expand...
 
E

engilo

thank you!

RagDyer said:
I assume when you said to return "yo" from the *4th* column, you really
meant the 3rd column (Column C).

This *array* formula will return the *first* match in Column C:

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

Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead
oft the regular <Enter>, which will *automatically* enclose the formula in
curly brackets, which *cannot* be done manually.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


it.
Click to expand...
 
Top