CODEWITHSUNDEEP
google-sheetslookup
kelp
kelp

Reputation: 13

(google sheets or excel) How can I find the closest matching value above the current cell, and return a different column in that same row?

I asked this question here, but I've hit another roadblock. I've included a visual below of what I'm trying to achieve.

example of ideal results

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 above the current row where Column A=x and Column B is any 3 digit number. The first part (where Column A=x) is simple, but I'm having trouble with the second part, where Column A <> x. In the linked question, I was able to use the solution to find the closest value above the current cell where Column A=x, but I have not been able to figure out how to find the closest value above the current cell where Column A=x AND Column B is any 3 digit number. Any suggestions would be much appreciated! Thank you!

Upvotes: 0

Views: 109

Answers (1)

player0
player0

Reputation: 1

try:

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

ISNUMBER will check for the digits and LEN will check for 3 numbers

Upvotes: 1

Related Questions