Lookup first value backwords

  • Hello experts,

    I am trying to find a way to use lookup or any other formula to return the results of the first value bottom up.
    My data is an org codes. So, if the object code is (3), I want excel to give me the org code of the first (2) starting from the selected cell going up.

    1 120000
    2 120001
    3 120002
    3 120003
    3 120004
    2 120005
    3 120006
    3 120007

    So, 3 120006 should return the value of 2 120005 not 2 120001.

    I hope my explanation is clear enough.

    Any thoughts?!!

    I've tried lookup, vlookup, index, match, nothing worked so far or I night not used it right !

  • Re: Lookup first value backwords


    You haven't incorporated the fact that the last number is the last before both the columns match both inputs, i.e. 3 and 120006.

    With your formula you will get 120005 if the user enters 3 and 120003, where I think the use would want 2 and 120001 in that case.

    So I would guess that if data is in A1:B8, and inputs are in D1:E1 (i.e. 3 and 120006, respectively), then formula like:


    would get the 120005. and to get 2, simply


    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

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