CODEWITHSUNDEEP
excelvlookup
Allwyn P
Allwyn P

Reputation: 43

Vlookup based on format

Purely excel query here.

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)

Any ideas on how to progress? Thanks in advance

Upvotes: 0

Views: 110

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

You have not supplied enough sample data to be sure, but if the "matching cell" might be a number instead of a text string, then replace lookup_value with

IFERROR(--MID(A2,13,3),MID(A2,13,3))

Not Tested:

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

Upvotes: 1

Related Questions