Lookup value is one row and return the value of a cell relative to it

[BKGLTS]

Hello,

I thought I knew how to do this, but for some reason I can't get it to work.

For example:

This formula returns the value I need to look up in range $W$16:$DUR$16.
COUNTIF($W$15:$DUR$15,"<"&S$18)+V18

Once the value is found in that range I need the formula to return the value of the cell directly above it.
So, the value I need returned will be somewhere in the $W$15:$DUR$15 range.

I have tried all kinds of OFFSET, CELL, ADDRESS, INDEX, MATCH formulas, but can't get it.

Please help.

Thank you!
B

 

[Marcelo Branco]

Not sure i understand what you need

Maybe
=INDEX(W15:DUR15,MATCH("myvalue",W16:DUR16,0))
replace "myvalue" with the value searched.

M.

 

[Fluff]

Maybe
=INDEX(W$15:DUR$16,1,MATCH(COUNTIF($W$15:$DUR$15,"<"&S$18)+V18,W$16:DUR$16,0))

I'm way to slow

 

[BKGLTS]

Thank you both!
Works great!

 

[Fluff]

Glad we could help & thanks for the feedback