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.


    Any advice would be helpful.


    Thanks.

    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!

Register Yourself Login