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?

 

[mvptomlinson]

Hi Steve, you can just enter the two vlookup formulas sequentially in M1, e.g.

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

 

[stevelucky]

Hi Steve, you can just enter the two vlookup formulas sequentially in M1, e.g.

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

That's awesome, I didn't know that. One question: how do I tie that comma in to the 2nd VLOOKUP so that if nothing is returned for that VLOOKUP, no comma is returned either?

Right now, if there's nothing returned in the 2nd VLOOKUP, this is what I get:

Value1,

When I really just need:

Value1

 

[mvptomlinson]

You can test for errors or blanks, and return blank from that function, e.g.

=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)))

 

[stevelucky]

That worked PERFECTLY! Thank you so much. The world will be safe for another day.

 

[mvptomlinson]

Glad to hear it.. I wouldn't want the world's existence to depend solely on my Excel abilities.

 

[stevelucky]

You can test for errors or blanks, and return blank from that function, e.g.

=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)))

Actually, how do I keep it from returning an #N/A if there's no value in either H2 or G2? I didn't think about that. There will be instances where those fields are just not filled out and I'd like the return to be blank.

 

[mvptomlinson]

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.

 

[stevelucky]

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.

Well, it was accurate but misleading. If G2 exists in the first sheet, it will definitely also exist in the second, but I didn't take into account where NOTHING exists in G2. Yes, if the first results in an error or blank, it can just stop the whole process.

 

[mvptomlinson]

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