look for nearest specific value and result value from a cell in same row

In column C, I would like the formula to search for the nearest row (looking only in above rows) with the same value as in column A, and result is the value B of the found row. Here's an example:


A B C
2 5 0
1 9 0
3 8 0
1 2 9
2 4 5
4 4 0
1 9 2
1 6 9

So if we look at the second last row as the 1 9 2, I will explain how those numbers came about. The user enters the number in rows A and B, they just happen to be 1 and 9. However the formula entered the number 2 in row C by looking for the value 1 (column A) nearest above it, not below! Then it shows the value 2 (from column B of the found row).

If you look at any row in my above example, the result in (C) of any row is correct. Sometimes the result is 0, if there is nothing found by the formula.

Thanks!

Could you give some more examples please, I can't quite follow the logic (posting a sample sheet would help)

Hi,

Perhaps, in C2 and copy down:

=IF(COUNTIF(A$1:A2,A2)=1,0,LOOKUP(2,1/(A$1:A1=A2),B$1:B1))

Regards

You're welcome.