# VLOOKUP First X Characters In Cell

• I need to look up the first three characters in a cell and return a corresponding letter from another sheet.

Sheet 1:
A1: 201-56325
A2: 501AN52645
A3: 222-5611-6321

B1-B3: blank

Sheet 2:
A1: 201
A2: 222
A3: 501

B1: H
B2: J
B3: K

I need a formula to use in Column B of Sheet 1 that will look up only the first three numbers of the cell value in Column A and return the corresponding letter from Column B of Sheet 2.

For example, when I enter this mystery formula into cell B1 on Sheet 1, the result should be H.

I tried using VLOOKUP along with LEFT(A1,3) but I keep getting #N/A. I have too many possible letters for a series of "IF" statements.

Thanks.

• Re: Look Up First 3 Characters In Cell

You could try

=VLOOKUP(LEFT(A1,3)+0,Sheet2!\$A\$1:\$B\$3,2,FALSE)

VBA Noob

• Re: Look Up First 3 Characters In Cell

VBA Noob has given a good solution. The reason why the vlookup formula doesn't work is because the Left() function outputs the value as string (text) and the numbers on your second sheet are literally....numbers (value). The other option you can use alternative to VBA Noob's suggestion, is to convert the values in your second sheet to text by inserting ' (ofcourse, where the data is big, this will be a tedious process - but you can use it at the time of input of data)

• Re: Look Up First 3 Characters In Cell

Wow.

Thankyouthankyouthankyou!

This solution works perfectly, and I was able to use Sheet2!A:B within the formula to include future data.

VBA Noob - Thank you!

raj - Thanks for explaining! I thought the text vs. number thing was the issue but I couldn't figure out how to fix it aside from formatting cells, which didn't work.

You both rock. This has been driving me crazy for 2 days.

-Ab

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!