The idea
Most spreadsheet jobs hit the same wall: the value you need is in another table. An order line carries a product_id, but today's price sits in the catalog. A lookup bridges the two. You give it a key (143), a place to search and the column you want back, and it returns the matching cell.
The mechanism is a scan. The lookup walks down one column, the key column, comparing each cell with your key. At the first match it remembers the row. Then it reads another column of that same row and hands you that cell. Find the row, then read across.
Figure 1
VLOOKUP scanning the catalog for product 143, then for product 121
| product_id | name | category | unit_price |
|---|---|---|---|
| 141 | Thatcher Acacia Dutch Oven | Kitchen | 13.99 |
| 142 | Upland Acacia Serving Platter | Kitchen | 38.99 |
| 143 | Pine Cast Iron Carafe | Kitchen | 289.00 |
| 144 | Hazel Porcelain Cutting Board | Kitchen | 129.00 |
| 145 | Cove Cast Iron Dutch Oven | Kitchen | 10.99 |
| lookup_value | formula | returns |
|---|---|---|
| 143 | =VLOOKUP(143,A2:D6,4,FALSE) | 289.00 |
| 121 | =VLOOKUP(121,A2:D6,4,FALSE) | #N/A |
| 121 | =IFERROR(VLOOKUP(121,A2:D6,4,FALSE),"not in catalog") | not in catalog |
- A Harbor & Pine order line says product 143. The formula wants its price.
VLOOKUPstarts at the top of the first column of the table and compares each key with 143. - 141 is not 143, so move down. 142 is not 143 either. The scan is a loop that stops at the first match.
- 143 matches in the third row. The scan stops, and the lookup now holds one fact: row 3. The column number 4 picks
unit_pricefrom that row, 289.00. - Product 121 (a floor lamp) is not in a kitchen catalog. The scan reaches the end without a match and returns
#N/A, which means not available, not zero. IFERRORcatches that error and returns the text you chose instead. Use it for a missing key you expect, not to hide mistakes.
Why it exists
The alternative is typing prices into every order line. Then a price change means finding and fixing thousands of cells, and one forgotten cell quietly misprices an order. With a lookup the price lives in the catalog, once, and every line asks for it. It is the same reason a database joins tables instead of copying columns.
In SQL this is a JOIN on product_id. In Excel you do it one formula at a time, which is also why it is slow on very large sheets and why lookup columns are the first place an analyst checks when a number looks wrong.
How it works under the hood
VLOOKUP: key, table, column number, match type
=VLOOKUP(key, table, column_number, FALSE) always searches the leftmost column of the table you give it. The column number counts from that first column: in a table A2:D6, column 4 is D. Two traps follow from that. Insert a column inside the table and the number no longer points at what you meant. And the key must be in the first column; if it isn't, VLOOKUP cannot see it.
The last argument decides how the scan behaves. FALSE means exact match: stop at the first cell equal to the key, or return #N/A. TRUE, or leaving it out, means approximate match: it assumes the first column is sorted and returns the last row whose key is not larger than yours. That is right for bands (a table of thresholds), and silently wrong for ids. Forgetting the FALSE is the most common lookup bug, because on a mostly sorted list it still returns plausible values.
Missing keys
An exact lookup that finds nothing returns #N/A. That is information: this id is not in the catalog. IFERROR(formula, "not in catalog") swaps the error for a message, and IFNA does the same but only for #N/A, so a typo in the formula is not hidden. Never wrap a lookup in IFERROR(…, 0) for money: a zero price looks like data.
HLOOKUP and XLOOKUP
HLOOKUP is the same scan turned sideways: it searches the first row and returns from a lower row. Use it when the table runs across, like quarters in the header. XLOOKUP(key, search_range, return_range) separates the two columns instead of using a column number. It defaults to exact match, can look left, and takes an argument for what to return when nothing matches. It needs Excel 2021 or Microsoft 365, and Google Sheets has it.
INDEX and MATCH
Before XLOOKUP, the way to look left was to split the job in two. MATCH(key, range, 0) scans a range and returns the position of the key. INDEX(range, position) returns the cell at that position of another range.
Figure 2
INDEX/MATCH: one function finds the position, the other reads it
| position | product_id | name | unit_price |
|---|---|---|---|
| 1 | 141 | Thatcher Acacia Dutch Oven | 13.99 |
| 2 | 142 | Upland Acacia Serving Platter | 38.99 |
| 3 | 143 | Pine Cast Iron Carafe | 289.00 |
| 4 | 144 | Hazel Porcelain Cutting Board | 129.00 |
| 5 | 145 | Cove Cast Iron Dutch Oven | 10.99 |
| step | formula | returns |
|---|---|---|
| MATCH | =MATCH("Pine Cast Iron Carafe",C2:C6,0) | 3 |
| INDEX | =INDEX(B2:B6,3) | 143 |
- Here the key is the product name and the answer is the product_id, which sits to the left of the name.
VLOOKUPcan only return columns to the right of the key. MATCHscans the name column and returns the position of the first exact match: 3. Position, not a value.INDEXtakes a column and a position and returns the cell there. Position 3 of the product_id column is 143. Nest the two and the lookup works in any direction.
Because each function takes its own range, you can look up a name and return an id, or return a column that is not even next to the key. The pair also survives inserted columns, since it points at columns directly instead of counting them.
Decisions: IFS reads top to bottom
Banded rules use the same "stop at the first hit" idea. IFS(test1, value1, test2, value2, …) evaluates the tests in order and returns the value of the first one that is TRUE.
Figure 3
IFS tests in order and stops at the first TRUE
-
C2>=75free shipping: 0 -
C2>=354.95 -
TRUEeverything else: 7.95
- Order 12418 has a subtotal of 37.98.
IFSreads the first test: is 37.98 at least 75? - No, so it moves on without using that value. Second test: is 37.98 at least 35?
- Yes.
IFSreturns 4.95 and never looks at the third test. A subtotal of 20.00 would fall through both and reachTRUE, the catch-all.
That means order matters. Test the largest threshold first; if you test >=35 first, an order of 682.96 gets 4.95 and never reaches the free-shipping band. A final TRUE test acts as "otherwise". Without it, an order that fails every test returns #N/A. AND, OR and NOT combine several conditions into one TRUE/FALSE inside any of these tests.
Checking a lookup before you trust it
Pick three rows: one that should match, one that should not, and one near the edge of the table (the first or the last key). Work out each answer by eye and compare it with what the formula gives. Then count the errors in the result column. A handful of #N/A for ids you know were dropped is normal; a column full of them usually means the key has stray spaces, a different type or a wrong column number.
Common misconceptions
"VLOOKUP returns the closest match"
Only with approximate match. With FALSE it returns the exact key or an error. If you see a value for an id that doesn't exist, check whether the last argument is missing.
"A lookup can return a column to the left of the key"
VLOOKUP cannot, because it reads the leftmost column and counts rightwards. Use XLOOKUP or INDEX/MATCH. Rearranging the source table to make VLOOKUP work is how stale copies of data get born.
"If the lookup returns a value, the row is the right one"
It returns the first row that matches. If the key column has duplicates, later rows are never reached, and numbers stored as text never match numbers. When a result is surprising, check the key column for duplicates and for stray spaces.
Check yourself
One question. Answering it ticks this lesson off on the map; it never locks a level.
Not quite. Only approximate match (the last argument TRUE or left out) needs a sorted first column. With FALSE the scan simply starts at the top and stops at the key.Try another answer.
Right. The formula looks for 143 wherever it sits, so the sort order of an exact-match table never changes the answer.
Not quite. A lookup stores nothing between recalculations. It scans again every time and finds the row that holds 143 now.Try another answer.
Not quite. Sorting moves whole rows. The keys and their prices travel together, so the key is still found.Try another answer.
Lesson done: it is ticked on the map.