📎 This article includes 1 downloadable practice file ↓
In this article
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.
LOOKUP in practice
Where you will use it
- Grade from a sorted slab table
- Last non-blank value: LOOKUP(2,1/(A:A<>""),A:A)
- Tax slabs
Worked example
Slab rate from an illustrative slab table.
| Income | Formula | Result |
|---|---|---|
| 350000 | =LOOKUP(A2,{0,400000,800000,1200000,1600000},{"0%","5%","10%","15%","20%"}) | 0% |
| 900000 | =LOOKUP(A3,{0,400000,800000,1200000,1600000},{"0%","5%","10%","15%","20%"}) | 10% |
| 1600000 | =LOOKUP(A4,{0,400000,800000,1200000,1600000},{"0%","5%","10%","15%","20%"}) | 20% |
Results calculated in Excel.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Unsorted lookup range | LOOKUP needs ascending order |
| Exact matches | Use XLOOKUP for exact lookups |
Related functions
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Practice workbook: Lookup & reference17 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 35 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.