How to Do a Lookup Function in Excel

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. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. For “do a lookup function in excel”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected formula change, and only then apply the same setup to the full sheet. 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. That is what makes a calculation you can defend to a CFO or an auditor useful in real work: repeatable, auditable, and not dependent on memory or luck.

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. One habit worth forming early: name the cells that hold your inputs, so the formula reads in plain language instead of a string of cell addresses. A reviewer — or you in three months — can then follow the logic without decoding what B7 and D2 were supposed to mean, which is most of what makes a sheet maintainable.

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. Nothing on this page is behind a login or a download: the exact answer is at the top, and the detail below it is there for when you need it. That is the whole promise here — the answer first, and just enough context to make it stick. The short version of “do a lookup function in excel”: the answer is at the top of this page, the tool proves it on your own numbers, and the sections above explain why it holds so the next variation does not stump you. Excel rewards people who reference cells instead of typing values and who keep inputs separate from formulas, because that is what makes a result you can audit months later. Build it once, deliberately, with the live tool as a check, and you convert a one-off lookup into a reusable skill — which is the whole point of learning the why and not just the what.

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 XLOOKUP or INDEX/MATCH express the intent far more clearly.

Frequently asked questions