0

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:

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!

basic
  • 11,673
  • 2
  • 9
  • 26
kelp
  • 3
  • 2

2 Answers2

0

Try LOOKUP:

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

enter image description here

basic
  • 11,673
  • 2
  • 9
  • 26
  • This worked perfectly, thank you so much!! – kelp Nov 12 '21 at 22:25
  • Two questions: 1) Could you point me to information explain what the "1/(...)" in the lookup function does exactly and how that accomplishes looking for values in rows above? 2) This solution always returns the value from the current row (rows 1, 3, 6, and 9). What would be needed to exclude that row and only return values from rows above the current one (for example, I'd want to see 125 in C6 and 126 in C9) – Marjan Venema Mar 24 '22 at 11:31
0

try:

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

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124