VLOOKUP & compatibility
Read existing workbooks safely and make exact matching explicit.
By the end, you’ll be able to:
- Count a return column within the selected table
- Use FALSE for exact identifiers
- Recognize when a leftward lookup needs a different method
Understand the pattern you will find in existing files
VLOOKUP searches down the first column of a selected table and returns a value to its right. It remains useful when a workbook must calculate in older Excel editions.
For the product table A2:C5, SKU is the first table column, Price is the second and Stock is the third. The worksheet’s column letters do not determine the column index; the selected table’s starting point does.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | SKU | Price | Stock | Requested SKU | |
| 2 | P100 | 12 | 25 | P200 | |
| 3 | P200 | 8 | 0 | ||
| 4 | P300 | 20 | 9 | ||
| 5 | P400 | 5 | 40 |
Specify all four arguments
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])lookup_value is the key to find. table_array contains the search column first and return columns to its right. col_index_num is the one-based return-column position inside that table. range_lookup is FALSE (or 0) for exact matching, TRUE (or 1) for approximate matching; omitting it selects approximate matching.
For identifiers, write FALSE explicitly. Exact matching does not require sorted SKUs.
Worked example: price by SKU
=VLOOKUP(E2,A2:C5,2,FALSE) returns 8 for P200. Changing the column index to 3 returns stock of 0. Changing E2 to P300 returns a price of 20.
A missing exact key produces #N/A. A deliberate IFERROR wrapper can show a message, but it also catches errors other than missing matches. Investigate broken references and invalid column indexes rather than hiding them indiscriminately.
Approximate matching is a different business rule
A sorted rate band may intentionally use approximate matching to return the largest threshold not exceeding an input. The first lookup column must be sorted ascending for ordinary approximate VLOOKUP.
That behavior is unsuitable for an unknown product SKU: returning a nearby product is not a useful substitute. The browser exercises use exact FALSE matching only. Learn the approximate pattern with an explicit band table rather than accidentally creating it by omitting the fourth argument.
Check references before copying or changing columns
If copying a lookup down many rows, keep the source table fixed, for example $A$2:$C$5, while allowing the requested key row to move. A hard-coded column index can need updating after structural changes to the source.
VLOOKUP cannot directly return a column to the left of its first search column. XLOOKUP or INDEX with MATCH handles that pattern more naturally. In the workbook, test another SKU and an unavailable key before copying the formula across a large report.
Give it a try.
Work through each exercise, check your answer and use a hint whenever you need one. Formula tasks check changed inputs, including boundary cases.
0 of 4 exercises complete
The bounded practice checker accepts equivalent supported formulas, lowercase function names and text labels, extra spaces, and comma or semicolon separators. Array tasks check the complete result shape and values. It is a teaching tool, not a complete Excel engine. See supported behavior.
Take this with you
For exact VLOOKUP, put the key first in the table, count the return column within that table and write FALSE explicitly.
Practice in Excel
Download the course workbook, follow the tasks, save your completed .xlsx and check its supported formula tasks here. Native Excel features use a separate review checklist.
Download practice workbook Check my workbook ↗Technical references and lesson review
Original lesson content. Technical behavior checked against Microsoft documentation. Reviewed 2026-10-16.