Get Highest Near Value in Google Sheets with formulas

1
votes

Any optimal way to find near highest value in Google sheet using inbuilt functions.

I had already completed this using Google Apps script but I need a more reliable solution using Google Sheet Functions.

enter image description here

https://docs.google.com/spreadsheets/d/1C58-78X5o5HHTrN-JiNhpZbXa0gfOd3Ov4Qw0KbmVgo/edit?usp=sharing

1
google-sheetstransposegoogle-sheets-formulagoogle-sheets-querygs-vlookup

1 Answers

1
votes
=VLOOKUP(B2,{{0; SORT(UNIQUE(TRANSPOSE(SPLIT(TRIM(QUERY(TRANSPOSE(QUERY(
 TRANSPOSE(E3:AF22),,999^99)),,999^99)), " "))))},
 {MIN(E3:AF22); QUERY(SORT(UNIQUE(TRANSPOSE(SPLIT(TRIM(QUERY(TRANSPOSE(QUERY(
 TRANSPOSE(E3:AF22),,999^99)),,999^99)), " ")))), "offset 1", 0); ""}}, 2, 1)

0