Vlookup

JTL9161 · May 31, 2018

I am looking for something within a VLOOKUP formula.

I have the VLOOKUP searching using the data in cell B11 and it works fine, but what if cell B11 is empty and the data needed to search is in cell C11 or D11 or E11. Is there a way to tell VLOOKUP to check a range of cells if the original cell in empty for the search data? Maybe shouldn't be using VLOOKUP for this?

=VLOOKUP(B11,J60:M89,2,FALSE)

Thank you for your feedback.
Jamie

Joe4 · May 31, 2018

Well, you could nest a bunch of IF statements, i.e.

Code:

=IF(B11<>"",[COLOR=#333333]VLOOKUP(B11,J60:M89,2,FALSE),IF(C11<>"",[/COLOR][COLOR=#333333]VLOOKUP(C11,J60:M89,2,FALSE),...))[/COLOR]

But there may be other better ways of doing it, especially depending on what the answers to the following questions are.

Regarding cells B11, C11, D11, and E11,
- Will only exactly one of those cells have a value in it?
- Is the value in these cells that you are looking up always a certain length? If so, what is the length?

JTL9161 · May 31, 2018

Thanks for your reply

Unfortunately B11,C11,D11 and E11 could or could not have the same value in it. At least 1 of them will have something so 3 columns could be blank and 1 could have a value or all could have the same or 1 could have XYZ and the other 3 have ABCD or 2 could have XYZ and the other 2 ABCD I am pasting something from our database that will be several rows once I get row 11 figured out I will copy the formula down. Just trying to make some looking up easier on a daily basis.

Will your above one still work?

JTL9161 · May 31, 2018

I tried your formula and it appears to work...THANKS!

Joe4 · May 31, 2018

You are welcome.

If the entries were always a set number of characters long, like say 4, then you could do something like this instead:

Code:

[COLOR=#333333]=VLOOKUP([/COLOR][COLOR=#ff0000]LEFT(B11&C11&D11&E11,4)[/COLOR][COLOR=#333333],J60:M89,2,FALSE)[/COLOR]

JTL9161 · May 31, 2018

Sorry...got ahead of myself. After I enter the C column part of the formula I get an error of the D column

[FONT=&quot]IF(B11<>"",VLOOKUP(B11,J$60:M$89,2,FALSE),IF(C11<>"",(VLOOKUP(C11,J$60:M$89,2,FALSE),IF(D11<>"",VLOOKUP(D11,J$60:M$89,2,FALSE)))

The formula you typed contains an error. When I did it all the way out to the E column part I get your formula has to many arguments.

Am I missing something?

[/FONT]

Joe4 · May 31, 2018

I think you have an issue with your parentheses.

Try:

Code:

=IF(B11<>"",VLOOKUP(B11,J$60:M$89,2,FALSE),IF(C11<>"",VLOOKUP(C11,J$60:M$89,2,FALSE),IF(D11<>"",VLOOKUP(D11,J$60:M$89,2,FALSE))))

Tetra201 · May 31, 2018

The following formula will take the lookup value from the first non-empty cell in range B11:E11 :

=VLOOKUP(INDEX(B11:E11,MATCH(TRUE,INDEX(B11:E11<>"",0),0)),J60:M89,2,FALSE)

JTL9161 · Jun 1, 2018

Thank you! Seems to work fine.

Vlookup

JTL9161

Well-known Member

Excel Facts

Joe4

MrExcel MVP, Junior Admin

JTL9161

Well-known Member

JTL9161

Well-known Member

Joe4

MrExcel MVP, Junior Admin

JTL9161

Well-known Member

Joe4

MrExcel MVP, Junior Admin

Tetra201

MrExcel MVP

JTL9161

Well-known Member

Similar threads

Forum statistics

Share this page

Vlookup

Well-known Member

Excel Facts

MrExcel MVP, Junior Admin

Well-known Member

Well-known Member

MrExcel MVP, Junior Admin

Well-known Member

MrExcel MVP, Junior Admin

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock