I have a unique VLOOKUP question that I have not been able to solve with
I have a list that I need to lookup the relevant model names for. The
list is both numeric, and alphanumeric. I converted to text, and then
sorted so that, for example, 12B1 appeared after 1200, however, I cannot
get the VLOOKUP to lookup the 12B1 correctly.
To further complicate matters, I want it to only look up by the first
four digits in the list.
If I use the formula:
=VLOOKUP(--LEFT(A302,4),ModelName!A$2:D$2001,3,FALSE), it will look up
the numerics just fine, but not the alphanumerics.
I created a second column to convert the 5 digits to 4, and then did a
VLOOKUP on that one for the alphanumeric using this formula:
=VLOOKUP(TEXT(B306,"@"),ModelName!A$2:D$2001,3,FALSE) and that worked.
Problem is, each formula will not work on the opposite data type.
So, I have a few options. If I can get both of the formulas to only look
up using the first four digits, that would be great. If I have to, I can
convert the whole column to 4 digits, and do a VLOOKUP on that, but if
possible I'd like one formula that I can use on the whole spreadsheet
rather than having to apply two different formulas after I have parsed out