LookupLevel: ExpertAvailable in: Excel 2007+
In this article
- Syntax
- Examples
- Example 1
- Example 2
- Related functions
LOOKUP is an older function that needs sorted data for normal use — but its array form powers the well-known “last match” trick in every version.
Syntax
=LOOKUP(lookup_value, lookup_vector, [result_vector])
| Argument |
What it means |
lookup_vector |
Must be sorted ascending for normal lookups. |
Examples
Example 1
=LOOKUP(2, 1/(A2:A500=F2), C2:C500)
Last price for the product in F2 (works in old Excel without Ctrl+Shift+Enter).
Example 2
=LOOKUP(2, 1/(B:B<>""), B:B)
Last non-empty value in column B.
💡 Why 2? The 1/(condition) array contains 1s and #DIV/0! errors; LOOKUP can never find 2, so it returns the last 1 it saw — the last match.
XLOOKUP · INDEX · MATCH
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
✨ Ask AI about this articleStuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong