VLOOKUP Not working

[caverchik]

Hello,

I have a unique VLOOKUP question that I haven't been able to solve wit
other googling.

I have a list that I need to lookup the relevant model names for. Th
list is both numeric, and alphanumeric. I converted to text, and the
sorted so that, for example, 12B1 appeared after 1200, however, I can'
get the VLOOKUP to lookup the 12B1 correctly.

To further complicate matters, I want it to only look up by the firs
four digits in the list.

If I use the formula
=VLOOKUP(--LEFT(A302,4),ModelName!A$2$2001,3,FALSE), it will look u
the numerics just fine, but not the alphanumerics.

I created a second column to convert the 5 digits to 4, and then did
VLOOKUP on that one for the alphanumeric using this formula
=VLOOKUP(TEXT(B306,"@"),ModelName!A$2$2001,3,FALSE) and that worked.

Problem is, each formula won't work on the opposite data type.

So, I have a few options. If I can get both of the formulas to only loo
up using the first four digits, that would be great. If I have to, I ca
convert the whole column to 4 digits, and do a VLOOKUP on that, but i
possible I'd like one formula that I can use on the whole spreadshee
rather than having to apply two different formulas after I've parsed ou
the data.

Help

 

[Mazzaropi]

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

Dear *caverchik*, Good Afternoon.

I believe that you can manage the two situations on the same formul
without transform the formula into a nightmare.

Try this one:
=IF(ISTEXT(A302),VLOOKUP(LEFT(A302,4),MODELNAME!$A$2:$D$2001,3,FALSE),VLOOKUP(VALUE(LEFT(A302,4)),MODELNAME!$A$2:$D$2001,3,FALSE)

Adapt the range or cells to your reality, if necessary.

Tell me it it worked for you

 

[caverchik]

Dear *caverchik*, Good Afternoon.

I believe that you can manage the two situations on the same formul
without transform the formula into a nightmare.

Try this one:
=IF(ISTEXT(A302),VLOOKUP(LEFT(A302,4),MODELNAME!$A$2:$D$2001,3,FALSE),VLOOKUP(VALUE(LEFT(A302,4)),MODELNAME!$A$2:$D$2001,3,FALSE)

Adapt the range or cells to your reality, if necessary.

Tell me it it worked for you.

It worked for a majority of the fields. It did not work for some o
them, and there doesn't seem to be a pattern as to what those were. I'l
work at it some more. Thank you

 

[Mazzaropi]

It worked for a majority of the fields. It did not work for some o
them, and there doesn't seem to be a pattern as to what those were. I'l
work at it some more. Thank you!

Dear *caverchic*, Good Evening.

Attach here a sample of your worksheet.

Please, put there values that work and values that don´t work to easie
to find a solution.

We can find a solution as a team