My lookup table has values that have special formatting, example
the code that am using is this
Output of
but the vlookup always gives
,
In the table if you
that is why vlookup is failing, so what can be done here? any suggestions?
I have checked the obvious and that didnt work either,
Any ideas on how to progress? Thanks in advance
Code | Variant name |
---|---|
000 | No sub Variant |
Excel Formula:
VLOOKUP((MID(A2,13,3),'[New Variant Name.xlsx]Legend for New Variant Number'!$K$5:$L$49,2,FALSE)
Output of
Excel Formula:
MID(A2,13,3) is 000
Excel Formula:
#N/A
In the table if you
Excel Formula:
check the value of code (000) in formula bar, u get 0. and the corresponding cell in the lookup table is 0
I have checked the obvious and that didnt work either,
Excel Formula:
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)
Excel Formula:
Value of cell A2 is UPE00819-YCU000-NA
Excel Formula:
Value of cell K6 (where 000 is present) is 0
Any ideas on how to progress? Thanks in advance