backward lookup

I have dates in column A in ascending order. In column B i have numbers. There are no blank cells. I want to do a lookup for a selected date, and return the number corresponding to that date (from column B). If the number is zero, i want to return the first non zero number BEFORE the selected date. So moving backwards. Is this doable?

Hi

Given your data in A1:A100 and B1:B100 you could use this formula:

=INDEX($B$1:$B$100,MAX(IF($A$1:$A$100<=D1,IF($B$1:$B$100<>0,ROW($A$1:$A$100)-ROW($A$1)+1))))

Confirmed with Ctrl+Shift+Enter (it's an array formula).

Best regards

Richard

Or this solution:

Add a help column C to index the data in column B, this column can you hide.

Lookup formulas like this can be pretty scary at first, but in my attachment I explains step by step how you find the right value using INDEX and MATCH formulas.

Try this to work with any numbers in column B

=LOOKUP(9.999999999999E+307,IF(A1:A100<=D1,IF( B1:B100>0,B1:B100)))

confirmed with CTRL+SHIFT+ENTER

If you just have integers in B

=1/LOOKUP(2,1/(B1:INDEX(B:B,MATCH(D1,A:A,0))))

=1/LOOKUP(2,1/(B1:INDEX(B:B,MATCH(D1,A:A,0))))

I guess you didn't find this one in Microsofts help file for Excel....
It's a cool trick. Took me some time to figure out how it works.....