How do I Double Conditional vlookup for 3 columns?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Try...

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

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

Hope this helps!
 
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.
 
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...
 
I have a problem, I am trying to apply this formula in a new spreadsheet and it is not working. Here is the issue.
I have several spreadsheet opened up in one excel file (one per each month). Each spreadsheet has my customers on Column A, my Item numbers on Column B and the Quantities sold of each Item on Column C. I want to have a final consolidated page where I can track how many Items XXX, were consumed by Customer XXX for each month of the year. Is this the right formula? Can somebody help me? Thanks.

P.S. I downloaded the files from Peachtree into spreadsheets.
 
Multiple VLookups

Two arrays of data:
Sheet1!A1:B5
Sheet2!F1:G20

=Vlookup(Vlookup("Text",Sheet1!A1:B5,2,false),Sheet2!F1:G20,2,False)

Assuming "Text" is in Sheet1!A1:A5, then the whole formula will look up the corresponding value from Sheet1!B1:B5 in the table Sheet2!F1:G20, and return the value in the G column.
 
Is there any recent activity on this thread?
I need help with an excel question.
 
Back
Top