VLOOKUP in Excel, practised on a real sheet

VLOOKUP finds a value in the first column of a table and brings back something from the same row. The sheet above is the lesson: type each formula into its cell and press Enter. This page walks through the same steps for reading, with an example worked out by the same formula engine.

What VLOOKUP does

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value

What to find. Usually a cell that holds it, such as a product code.

table_array

The table to search. VLOOKUP only searches the first column of it, so the codes have to be on the left.

col_index_num

Which column of the table to bring back, counted from the table's own left edge: its first column is 1, whatever its letter on the sheet.

range_lookup

FALSE or 0 for an exact match. Leave it out, or write TRUE, and VLOOKUP settles for a near match instead. That only makes sense on a sorted first column.

A worked example

This sheet comes from the lesson, set in the Bike shop. Column A holds stock codes, and G2 holds the code to look for: H-23.

In G3, =VLOOKUP(G2,A2:D6,2,FALSE) runs down column A until it meets H-23, then reads column 2 of that row: Helmet. For the price, only the column number changes: =VLOOKUP(G2,A2:D6,3,FALSE) gives 59. Stock is the fourth column, so =VLOOKUP(G2,A2:D6,4,0) gives 3.

Always say FALSE, or 0

The last argument is optional, and that is the trap. Without it, VLOOKUP assumes column A is sorted and takes the nearest match it can find. These codes aren't sorted, so it gives either #N/A or, worse, an answer from another row with no error to warn you. On this sheet =VLOOKUP(G2,A2:D6,2) gives E-bike, not Helmet, and =VLOOKUP(G2,A2:D6,4) gives 35, not 3.

FALSE and 0 mean the same thing. Write one of them every time you look up a code, a name or an ID, and VLOOKUP never guesses. The lesson names this mistake when you make it.

Filling a lookup down: lock the table

Below the lookup, F8 to F11 hold four orders. G8 looks up the first one with =VLOOKUP(F8,$A$2:$D$6,3,FALSE), which gives 1,349. Fill it down to G11 and each row looks up its own order.

When a formula is copied, its references move with it. F8 becomes F9, F10 and F11, which is what you want for the order code. The table mustn't move, so it gets dollar signs: $A$2 means column A and row 2, always. Type the range, then press F4 to add them (fn + F4 on most Mac keyboards; Cmd + T also works in Excel for Mac).

Without the dollar signs the table slides down one row with every copy. Two rows down it starts at A4, so any order whose code sits higher up the table comes back #N/A. The lesson checks for this before you fill: an unlocked formula in G8 is right for that one row, and it is refused with a note to lock it.

When the code isn't there: #N/A and IFERROR

Type a code that isn't in the list, such as Q-33, and every lookup on it shows #N/A. It means no match, not that the formula broke. Check for stray spaces, a typing slip, or a number stored as text: to VLOOKUP, the text "1042" and the number 1042 are different things.

To show something friendlier, wrap the lookup in IFERROR, which gives its second argument whenever the first is an error: =IFERROR(VLOOKUP(G2,A2:D6,2,FALSE),"Not found") gives Not found. Use it once you know why lookups fail. It hides every error, the ones you didn't expect too.

XLOOKUP, the newer way

XLOOKUP takes what to find, where to look and what to bring back, as separate ranges: =XLOOKUP(G2,A2:A6,C2:C6). There's no column number to count, it matches exactly unless told otherwise, it can bring back a column to the left of the codes, and it keeps working when someone inserts a column. Its fourth argument says what to show when nothing matches, so it needs no IFERROR: =XLOOKUP(G2,A2:A6,C2:C6,"Not found") gives Not found.

XLOOKUP needs Excel 2021 or later, or Microsoft 365. Excel 2019 and older show #NAME? instead. If the file goes to people on older versions, use VLOOKUP.

The lesson, step by step

All ten steps of the sheet above, with what each one gives on this example and the mistakes it checks for.

1. 1 Look up the product

   In G3: =VLOOKUP(G2,A2:D6,2,FALSE) gives Helmet.

   Named mistake: =VLOOKUP(G2,A2:D6,2) gives E-bike. Without FALSE, VLOOKUP settles for a near match. On codes like these that gives a wrong answer or #N/A. Add ,FALSE at the end.

2. 2 Bring back the third column

   In G4: =VLOOKUP(G2,A2:D6,3,FALSE) gives 59.

   Named mistake: =VLOOKUP(G2,A2:D6,2,FALSE) gives Helmet. That's still column 2, the product. Price is the third column of the table, so the number is 3.

3. 3 The fourth column, exact on purpose

   In G5: =VLOOKUP(G2,A2:D6,4,0) gives 3.

   Named mistake: =VLOOKUP(G2,A2:D6,4) gives 35. Without the 0, VLOOKUP assumes column A is sorted and settles for a near match. These codes aren't sorted, so it gives a wrong answer or #N/A. Add ,0 at the end.

4. 4 Now the price, the newer way

   In G4: =XLOOKUP(G2,A2:A6,C2:C6) gives 59.

5. 5 Price the first order

   In G8: =VLOOKUP(F8,$A$2:$D$6,3,FALSE) gives 1,349.

   Checked too: Right for this row, but fill it down and the table slides down with it, so the last orders show #N/A. Lock it as $A$2:$D$6.

6. 6 Price every order

   Fill G8 down over G8:G11. The last one, G11, becomes =VLOOKUP(F11,$A$2:$D$6,3,FALSE).

   They give 2,499 29 59

7. 7 Ask for a code that isn't there

   Type Q-33 in G2.

8. 8 Catch the error

   In G3: =IFERROR(VLOOKUP(G2,A2:D6,2,FALSE),"Not found") gives Not found.

9. 9 XLOOKUP has it built in

   In G4: =XLOOKUP(G2,A2:A6,C2:C6,"Not found") gives Not found.

10. 10 Change the question

    Type P-36 in G2.

In real Excel

Everything here works the same in Excel on Windows and on a Mac. VLOOKUP ignores capitals, so a code typed in lower case still finds its row. It can't bring back a column to the left of the one it searches; XLOOKUP, or INDEX with MATCH, can. And if someone inserts a column inside the table, a VLOOKUP's column number doesn't change with it, so it quietly brings back the wrong column. That is the strongest reason to move to XLOOKUP once everyone has it.

Keep going

1. 8
   Next up is Text: text functionsJoin first and last names, clean up stray spaces and capitals, split an email.
2. 6
   Conditions: SUMIF and COUNTIFTotals and counts that only include the rows you ask for.
3. 10
   Month end: month-end reportThe final sheet: a real report asked in plain words, no formulas shown.