Excel lookup upwards

0
votes

I am trying to lookup data that is the first occurrence of an ID looking upwards in excel. I would like to stop looking upwards when the cell value is ID is 0.

I have used the following to find the first occurrence when looking upwards:
In Cell D8:
=LOOKUP(2,1/($A$2:A8=C8),$B$2:B8)
In Cell D9:
=LOOKUP(2,1/($A$2:A9=C9),$B$2:B9)
For the case of desired outcome = 13 this works, however when there are consecutive IDs I would like to return the top most non-zero data field. Example desired outcome = 15 with the current formula returns 0.

How can I continue looking upwards until the top most ID = 2 before ID =0?

+----+------+--------------+-----------------+
| ID | Data | Lookup Value | Desired Outcome |
+----+------+--------------+-----------------+
| 2  | 18   |            1 | No Formula      |
+----+------+--------------+-----------------+
| 1  | 25   |            2 | No Formula      |
+----+------+--------------+-----------------+
| 0  | 0    |            0 | 0               |
+----+------+--------------+-----------------+
| 1  | 13   |            0 | 0               |
+----+------+--------------+-----------------+
| 2  | 15   |            0 | 0               |
+----+------+--------------+-----------------+
| 2  | 0    |            0 | 0               |
+----+------+--------------+-----------------+
| 2  | 0    |            1 | 13              |
+----+------+--------------+-----------------+
| 1  | 0    |            2 | 15              |
+----+------+--------------+-----------------+
| 0  | 0    |            0 | 0               |
+----+------+--------------+-----------------+
| 0  | 0    |            0 | 0               |
+----+------+--------------+-----------------+
2
excelexcel-formula
change 1/($A$2:A8=C8) to 1/(($A$2:A8=C8)*($B$2:B8<>0)) And wrap in IFERROR: =IFERROR(LOOKUP(2,1/(($A$2:A8=C8)*($B$2:B8<>0)),$B$2:B8),0) – Scott Craner
Thanks Scott - solves a potential issue in C8 - still struggling with C9 returning first value when looking upwards rather than last value – Spaghetti
It works for me, see my answer. – Scott Craner
You are right - Formula mistake on my side - Thanks!! – Spaghetti

2 Answers

1
votes

Put this in D4 and copy down:

=IFERROR(LOOKUP(2,1/(($A$2:A4=C4)*($B$2:B4<>0)),$B$2:B4),0)

enter image description here

1
votes

Put this in D4 and copy down:

=IFNA(LOOKUP(9^9,1/B$2:B4^-1/($A$2:A4=C4)),)