# 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.
Ex.
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

This will give you what yiu need:
=MAX(IF(A5:A12=3-1,B5:B12))
Confirm COntrol+Shift+Enter
(you can repalce 3 with let say D1)
But your numbers must be in ascending order

• Re: Lookup first value backwords

Quote from Armando Montes;683151

One way:
=LOOKUP(2,1/(A1:A8=3),B1:B8)

This is not about last (max) for 3 is for 3-1.

• Re: Lookup first value backwords

Robert,

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:

=LOOKUP(2,1/(A1:INDEX(A1:A8,MATCH(1,INDEX((A1:A8=D1)*(B1:B8=E1),0),0))=D1-1),B1:B8)

would get the 120005. and to get 2, simply

=D1-1

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

• Re: Lookup first value backwords

Yes you ae right NBVC I did not incorporate (did not see) the second condition.

## Participate now!

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