SOLVED

Vlookup issue

Copper Contributor

Hi Expertises, 

Need you help again. I have a problem in vlookup as below

 

73612347361231234
88Q234588Q567#VALUE!

 

I need to extract the first left 3 characters from the data under 3rd colume such as 736123 & 88Q567 for lookup purposes.

my formula is =VLOOKUP(VALUE(LEFT(c1,3)),$a$1:b$2,2,FALSE)

 

I got correct answer which is 1234 for 736, however, the formula is something wrong for 88Q. 

May I ask you help to fix 88Q issue? the answer should be 2345. Thanks a lot

 

2 Replies
best response confirmed by Lawrence_Lam_320 (Copper Contributor)
Solution

@Lawrence_Lam_320 

=VLOOKUP(IFERROR(VALUE(LEFT(C1,3)),TEXT(LEFT(C1,3),0)),$A$1:$B$4,2,FALSE)

This formula works for the sample data in my worksheet.

vlookup.png

Thank you so much.
1 best response

Accepted Solutions
best response confirmed by Lawrence_Lam_320 (Copper Contributor)
Solution

@Lawrence_Lam_320 

=VLOOKUP(IFERROR(VALUE(LEFT(C1,3)),TEXT(LEFT(C1,3),0)),$A$1:$B$4,2,FALSE)

This formula works for the sample data in my worksheet.

vlookup.png

View solution in original post