SOLVED

# Vlookup issue

Copper Contributor

# Vlookup issue

Hi Expertises,

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

 736 1234 736123 1234 88Q 2345 88Q567 #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

# Re: Vlookup issue

``=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.

# Re: Vlookup issue

Thank you so much.
1 best response

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

# Re: Vlookup issue

``=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.