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?

    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.


  • 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


Participate now!

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