VLOOKUP returns N/A when lookup column contains nested formula

Question

I've looked through the large number of "VLOOKUP"-relate questions, and can't seem to find a similar issue.

My lookup table's search column has data formatted like this:

HT100
HT101
HT102

But these values are formula based, where the original values were:

HT100 - a sweet truck
HT101 - another sweet truck
etc.

So the lookup table's search column actually contains the following formula:

=LEFT(A2,5)

where A2 refers to the full version of the data

The problem arises, however, when I get to a small subset of the data that looks like this:

LDR100 - a sweet front-end loader

Because it's 6 characters long, I can't use the formula above. Instead, I started using:

=LEFT(A2,SEARCH(" ",A2,1))

This chops the string at the first space character.

Problem:

VLOOKUP will return a match if the data is formatted "HT100".

VLOOKUP will return a match if the data is formatted "LEFT(A2,5)".

VLOOKUP will NOT return a match if the data is formatted "LEFT(A2,SEARCH(" ",A2,1))

My questions is: Why does VLOOKUP fail to return a match when the second argument of LEFT() is not a constant?

Note: VLOOKUP also fails to return a match if I put the second argument of LEFT() in another column and reference it, such as =LEFT(A2,G2) where G2 is a cell that contains the number 5 or 6 (as required) — Arne
Your LEFT(A2,SEARCH(" ",A2,1)) formula is leaving a space at the end of the results. Change the formula to =trim(LEFT(A2,SEARCH(" ",A2,1))) — Scott Craner
@ScottCraner it's as easy as that. Using =LEFT(A2,SEARCH(" ",A2,1)-1) also works. Thanks for the quick reply — Arne

InnaS InnaS · Accepted Answer · 2016-04-03T10:19:17

It's because you include space in your lookup value.
Try this:

=VLOOKUP(LEFT(A2, SEARCH(" ",A2,1)**-1**),$B$2:$C$4,2,FALSE)

or try not exact match:

=VLOOKUP(LEFT(A2, SEARCH(" ",A2,1)-1),$B$2:$C$4,2,**TRUE**)