A simplified approximate lookup over a sorted vector, plus a famous trick for the last non-blank value. It always assumes sorted data.

Syntax

=LOOKUP(lookup_value, lookup_vector, [result_vector])

• Banded lookup

  =LOOKUP(72,{0,60,70,80,90},{"F","D","C","B","A"}) → C

  Finds the largest value not exceeding 72.

• Last value in a column

  =LOOKUP(2,1/(A2:A100<>""),A2:A100) → Widget Pro

  The last non-blank cell in a column. The 1/(...) array holds 1s and errors; LOOKUP skips errors and stops at the last 1.

• Sorted names

  =LOOKUP("Gizmo",A2:A6,B2:B6) → 15

  Works only because the names happen to be in order; otherwise the answer is wrong.

• The old =LOOKUP(2,1/(range<>""),range) trick returns the last non-blank value.
• Use XLOOKUP for anything new; keep LOOKUP for the last-value trick.
• Handles arrays of any orientation automatically.
• Because it ignores errors it works where MATCH would fail.

• Wrong answers, not errors, on unsorted data.
• Text and numbers are treated as different types in the search.