VLOOKUP and HLOOKUP
The orders sheet stores item codes like C01, but Nisha's report must say "Cold coffee". The names and prices live on a separate Menu sheet. Copying them across by hand would break the moment a price changes. A lookup formula fetches the right value for each row, and updates itself when the menu does.
The Menu sheet:
| A | B | C | |
|---|---|---|---|
| 1 | code | item | price |
| 2 | C01 | Cold coffee | 120 |
| 3 | C02 | Masala chai | 40 |
| 4 | B01 | Banana bread | 90 |
| 5 | B02 | Croissant | 110 |
The rule
Find the key in the first column, return column N of that row.
=VLOOKUP(D2, Menu!$A$2:$C$5, 2, FALSE)- D2: the key to look for, here the order's item code.
- Menu!$A$2:$C$5: the table. VLOOKUP only ever searches its first column. The dollar signs lock the range so it does not slide as you copy down.
- 2: which column of the table to return, counting the first column as 1. Column 2 is
item; column 3 would beprice. - FALSE: exact match only. Always use it for codes, IDs and names.
If the key is not in the first column, the result is #N/A. Treat that as a data-quality flag: a typo, a stray space, or a new item missing from the menu.
HLOOKUP is the same idea turned on its side. It searches the top row of a table and returns row N, counting down from the top. Use it when your keys run across a row, such as months in a header: =HLOOKUP("Mar", A1:M3, 3, FALSE) finds "Mar" in row 1 and returns the value from the table's third row.
Newer versions of Excel also have XLOOKUP, which can search any column and return from any other. VLOOKUP is still everywhere in existing workbooks, so you must be able to read it.
The shortcut
Say the formula as a sentence: "Find this in the first column, move to column N, exactly." If the column you want to search is not the first one in the range, VLOOKUP cannot do it.
Find the key
Search column A of the Menu table for
B02. It is in the fourth row of the table (sheet row 5).
The trap is leaving out the last argument. Without FALSE, VLOOKUP assumes the first column is sorted and may quietly return a neighbouring row instead of an error.
Drills
Answer fast — trust the rule.
What does =VLOOKUP("C02", Menu!$A$2:$C$5, 3, FALSE) return?
Order 1003 has item code B01. Which formula returns its name?
A lookup for "C01 " (with a trailing space) returns #N/A. Why?
You want the price for "Banana bread" by searching the item column. Why does =VLOOKUP("Banana bread", Menu!$A$2:$C$5, 3, FALSE) fail?
Monthly totals sit in a table with months across row 1 and totals in row 2. Which function fits?
Remember
- Find the key in the first column, return column N of that row.
- Shortcut: read it as "find this, move to column N, exactly"; HLOOKUP searches the top row and returns row N.
- Trap: leaving out
FALSE, and expecting VLOOKUP to search any column but the first.