In Excel: use =LOOKUP(lookup_value, lookup_vector, result_vector) — the oldest lookup function, which always approximates and requires sorted data.
On this page8
Syntax
Arguments
| Argument | required / optional | Description |
|---|---|---|
lookup_value | required | The value to find. |
lookup_vector | required | A single sorted row or column to search. |
result_vector | optional | The row or column to return from, the same size as lookup_vector. |
Related functions
Sort the lookup vector ascending — LOOKUP requires it and gives no warning otherwise.
Type =LOOKUP( and enter the value to find.
Select the sorted lookup vector, then the result vector of the same size.
Press Enter. For anything that is not a banded lookup, prefer XLOOKUP instead.
Need it as an auditable file?
Ships inside the linked template — formula-driven, unlocked, audit-ready.
What this does
LOOKUP predates VLOOKUP and has no exact-match mode at all: it always finds the largest value less than or equal to what you asked for, which means unsorted data returns quietly wrong answers rather than an error. That makes it unsuitable for most modern work, and XLOOKUP or INDEX with MATCH should be preferred. It survives for two reasons. It is genuinely elegant for banded lookups — tax brackets, commission tiers, grade boundaries — where approximate matching is the point. And it appears in a great many inherited workbooks, so recognising it matters even if you never write a new one. The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “use the lookup function in excel”. Start on a copy or a tiny sample, keep the affected formula visible, and compare the result with the tool above before you touch the real workbook. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. The point is a calculation you can defend to a CFO or an auditor, but the practical win is that someone else can open the file and understand what happened without asking you.
A worked example
Commission bands with thresholds 0, 10000, 50000, 100000 in A2:A5 and rates 2 %, 4 %, 6 %, 8 % in B2:B5: =LOOKUP(65000, A2:A5, B2:B5) returns 6 % — the band the value falls into. The same thing in modern syntax is =XLOOKUP(65000, A2:A5, B2:B5, , -1), which at least states the approximation explicitly. LOOKUP is the function most likely to be silently wrong in an inherited workbook, which makes recognising it a debugging skill. A practical tip before you scale it up: build it once on a small block of test data, confirm the number against the tool on this page, and only then point it at your real sheet. That one habit catches almost every mistake while it is still cheap to fix, long before a wrong figure reaches a report or a colleague.
In Google Sheets
Some functions on this page are newer additions to Excel: they are in current Microsoft 365 and Excel for the web, while older perpetual Excel versions return #NAME?. Google Sheets maintains its own function list, so confirm each function exists there before relying on the same formula. Keep this page bookmarked for the next time the same question comes up. Better still, repeat the steps once in your own workbook — doing it yourself is what turns a copied answer into something you remember. Here is the takeaway for “use the lookup function in excel”: copy the answer if you are busy, but if you have a spare few minutes, rebuild the example in Excel yourself with the tool above open beside it. That single pass — type it, run it, watch the result move when you change an input — is what turns a formula you found into a technique you trust. Keep your inputs labelled and referenced, never hard-coded, and the same sheet stays correct and auditable as it grows. Done that way, you will not need to look this up again, and you will be the person others ask.
Common mistakes
- Using it on unsorted data, where it returns a wrong answer with no error at all — the single most dangerous property of this function.
- Expecting an exact-match option; there is none.
- Writing new formulas with LOOKUP when
XLOOKUPorINDEX/MATCHexpress the intent far more clearly.
Frequently asked questions
Should I use LOOKUP or VLOOKUP?
Neither for new work — use XLOOKUP where available, or INDEX with MATCH. LOOKUP is worth knowing mainly for reading inherited workbooks.
Does LOOKUP have an exact-match mode?
No. It always finds the largest value at or below the lookup value, which is why the data must be sorted.
When is LOOKUP still the right choice?
Banded lookups — tax brackets, commission tiers, grade boundaries — where approximate matching is exactly what you want.
Other ways people ask this
People reach this page typing “excel lookup function”, “excel lookup functions”, “how to use lookup function in excel” and “function lookup excel”, among other phrasings; whichever wording you used, the fix above is the one you want.
Why do people search for this in so many different ways?
Because the same task has many names. “excel lookup function”, “excel lookup functions”, “how to use lookup function in excel” all point at the one operation explained on this page, which is why they all lead here.