Multiple VLOOKUP's in one formula?

[stevelucky]

There might be a much better way to do what I'm trying to do, but I sure as heck can't figure it out and am hoping that one of the professionals here can.

I have a cell, M1 in my sheet that needs to return 2 separate value from 2 different tables on another sheet.


So, cell M2 will look at cell H2 and then go to the sheet called "LookupTables" and when it finds that value in column AA (that value WILL definitely exist once in that column) of that sheet will return the value in the 2nd column over. So now, cell M1 on my original sheet is displaying the appropriate value from column AB of my LookupTables sheet.

Next, I want it to get that value of cell G2 of the original sheet and look for that value in column G of my LookupTables sheet. When it finds that value, it should return the value from the 3rd column over (column I). If there is no value in that column, it should return nothing. One of the tricky parts is that I would like these 2 separate returned values to be separated by a comma. So the returned value in cell M1 will either look like:

value1

or

value1,value2

Does that make sense or am I rambling? Any thoughts?

 

[stevelucky]

Ok, then perhaps:

=IF(ISNA(VLOOKUP(H2,LookupTables!$AA:$AB,2,0)),"",IF(VLOOKUP(H2,LookupTables!$AA:$AB,2,0)="","",VLOOKUP(H2,LookupTables!$AA:$AB,2,0)&IF(ISNA(VLOOKUP(G2,LookupTables!$G:$I,3,0)),"",IF(VLOOKUP(G2,LookupTables!$G:$I,3,0)="","",", "&VLOOKUP(G2,LookupTables!$G:$I,3,0)))))

Perhaps, you sir, are great at this! Thanks exactly what I need. Thanks for taking your time to help me out.

 

[scled]

So "that value WILL definitely exist once in that column" is not accurate..

If the first VLOOKUP results in an error or blank, do you still want the second to even process?

If so, would the result be just the number returned, or ", #"? If you just returned the number, how would you know whether it was the result of the first VLOOKUP or second? Does that matter?

You could certainly check for errors/blanks in the first VLOOKUP function, but depending on your responses to the questions above there might be a few different paths.

hi, im having similar problem. my formula is:
=IF(VLOOKUP($A4,'Data'!$A$4:$R$1048576,18,FALSE)="Y","",VLOOKUP($A4,'Data'!$A$4:$R$1048576,17,FALSE)&", "&IF(VLOOKUP($A4,'Data'!$A$4:$W$1048576,23,FALSE)="P","","Update")&", "&IF(VLOOKUP($A4,'Data'!$A$4:$Z$1048576,25,FALSE)=VLOOKUP($A4,'Data'!$A$4:$Z$1048576,26,FALSE),"","Check Location"))

i want all vlookup formulas to process even if the first returns blank and then concatenate the results without the comma before the 2nd result.

 

[francescaexcel]

Hello Great tips... Just one question.. how do I need to change the formula if I want excel to continue with the second Vlookup even if the first one cause a #N/A? which mean in my case that I am looking for 1 value (that's why I'm using Vlookup) but I have different criteria that I can use to find it... which means that I can use the 1st vlook up with a #N/A and a second vlookup with the result I want, but if it find the result with the first vlookup I'd like the formula tu stop.. too much eh?!?!?!

Thanks a lot..

So "that value WILL definitely exist once in that column" is not accurate..

If the first VLOOKUP results in an error or blank, do you still want the second to even process?

If so, would the result be just the number returned, or ", #"? If you just returned the number, how would you know whether it was the result of the first VLOOKUP or second? Does that matter?

You could certainly check for errors/blanks in the first VLOOKUP function, but depending on your responses to the questions above there might be a few different paths.

 

[jruotolo]

Try something like this =IFERROR(VLOOKUP(A1,Sheet1!A1:F29,6,FALSE)&","&VLOOKUP(A2,Sheet1!A1:F29,5,FALSE),VLOOKUP(A2,Sheet1!A1:F29,5,FALSE))

 

[francescaexcel]

Thanks a lot!

And what if I'd like to add a third area to vlookup and the different vlookups will never realise togheter? Like if the first if not working (#N/A) use the second, if the second is not working (#N/A) use the third and potentially a fourth...

Try something like this =IFERROR(VLOOKUP(A1,Sheet1!A1:F29,6,FALSE)&","&VLOOKUP(A2,Sheet1!A1:F29,5,FALSE),VLOOKUP(A2,Sheet1!A1:F29,5,FALSE))

 

[jruotolo]

You just need to keep adding iferrror functions

=IFERROR(IFERROR(VLOOKUP(A1,Sheet1!$A$1:$F$29,6,FALSE)&","&VLOOKUP(A2,Sheet1!$A$1:$F$29,5,FALSE),VLOOKUP(A2,Sheet1!$A$1:$F$29,5,FALSE)),VLOOKUP(A3,Sheet1!$A$1:$F$29,4,FALSE))

 

[Aladin Akyurek]

@francescaexcel

What do these VLOOKUPs return, text or number, whenever they are successful?

 

[jruotolo]

Any data in the cell

 

[Oluwasheyifunmi]

Hello everyone, i am new here,i hope i'm on the right track.
How do i make a cell return a single vlookup formular even out of many formulars at once.
for instance, i have
=VLOOKUP(INDEX(Complaints_Refrence_List,A11),Complaints_Refrence_Box,2,FALSE)
=VLOOKUP(INDEX(Products_Refrence_List,A19),Products_Refrence_Box,2,FALSE)

how do i combine this together and make a cell return one of it at once not both formulars

 

[Aladin Akyurek]

Hello everyone, i am new here,i hope i'm on the right track.
How do i make a cell return a single vlookup formular even out of many formulars at once.
for instance, i have
=VLOOKUP(INDEX(Complaints_Refrence_List,A11),Complaints_Refrence_Box,2,FALSE)
=VLOOKUP(INDEX(Products_Refrence_List,A19),Products_Refrence_Box,2,FALSE)

how do i combine this together and make a cell return one of it at once not both formulars

Something like:

=IFERROR(
  VLOOKUP(INDEX(Complaints_Refrence_List,A11),
    Complaints_Refrence_Box,2,0),
  VLOOKUP(INDEX(Products_Refrence_List,A19),
    Products_Refrence_Box,2,0)