How can I find the closest matching value ABOVE the current cell, and return a different column in t-CodePudding

Working in google sheets, I am trying to find the closest matching value ABOVE the current cell and return a different column from that same row.

Here is a visual of what I'm trying to achieve:

For any row where Column A=x, just return the value in Column B to Column "Results". If Column A does not equal x, I want to return the closest (in distance) Column B value where Column A=x above the current row. The first part (where Column A=x) is simple, but I'm having trouble with the second part, where Column A <> x. I've tried various iterations of index match, vlookup, but I keep ending up with either the first or last match, rather than the closest (in distance) above.

Thank you so much!

CodePudding user response：

Try LOOKUP:

=ArrayFormula(LOOKUP(1,1/($A$1:A1="x"),$B$1:B1))

CodePudding user response：

try:

=INDEX(IF(B:B="",, VLOOKUP(ROW(B:B), IF(ISNUMBER(1*B:B), {ROW(B:B), B:B}), 2, 1)))