lookup using last 3 numbers

  • Hi All


    Any ideas how to lookup a value from another sheet using only the last 3 numbers from a cell value?


    i.e.
    Lookup value: Sheet1, Cell A1, which reads 002


    Range to lookup is Sheet2, Range A1:B20
    Column to return value from is Sheet2, Column B


    Sample values in Sheet 2 Column A are:


    A1 = CtrlCmd-001
    A2 = CtrlCmd-002
    ....


    Sample values in Sheet 2 Column B are:

    B1 = Sample Document Title #1
    B2 = Sample Document Title #2
    ....


    Answer expected is:


    Sheet1, Cell A1 = 001
    Sheet1, Cell B1= Sample Document Title #2


    I hope this is clear.


    Thanks

  • Hi All
    Just an update, the issue I was having was caused by custom formatting of a number.


    i.e. entering the number 1 was formatted into CtrlCmd-001 on enter key press


    So the actual value being looked up was only 1 digit not the 001 as required.



    Sorry to waste anyone's time


    Thanks

Participate now!

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