Vlookup based on formatting

[Alvin187]

My lookup table has values that have special formatting, example

Code	Variant name
000	No sub Variant

the code that am using is this

 VLOOKUP((MID(A2,13,3),'[New Variant Name.xlsx]Legend for New Variant Number'!$K$5:$L$49,2,FALSE)

Output of

 MID(A2,13,3) is 000

but the vlookup always gives

#N/A

,

In the table if you

check the value of code (000) in formula bar, u get 0. and the corresponding cell in the lookup table is 0

that is why vlookup is failing, so what can be done here? any suggestions?

I have checked the obvious and that didnt work either,

VLOOKUP(if((MID(A2,13,3)= 0,Text(0,000),MID(A2,13,3)),'[New Variant Name.xlsx]Legend for New Variant Number'!$K$5:$L$49,2,FALSE)

Value of cell A2 is UPE00819-YCU000-NA

Value of cell K6 (where 000 is present) is 0

Any ideas on how to progress? Thanks in advance

 

[Joe4]

It is important to understand that Excel formulas work off of the actual values in the cell, not the formatted/displayed value.
It sounds like the value in cell K6 is really the numeric value of 0, with special formatting to display three digits.

So we need our formula to return a numeric value, not a text one, like the MID function will return.
However, we can easily coerce the formula to return a numeric value by applying a simple calculation to it, like this:

=VLOOKUP((MID(A2,13,3)+0,'[New Variant Name.xlsx]Legend for New Variant Number'!$K$5:$L$49,2,FALSE)

Does that work?