VLOOKUP explained: stop being afraid of the most useful Excel formula

What VLOOKUP actually does

VLOOKUP searches for a value in the leftmost column of a table and returns a corresponding value from a specified column in the same row. The V stands for Vertical — it searches down a column rather than across a row.

The business problem it solves: you have two tables with a common identifier, and you want to pull information from one table into the other. Order IDs in one sheet, customer details in another. Agent codes in one table, agent names in a reference list. Product SKUs in a transaction table, product descriptions in a catalogue. VLOOKUP is the bridge.

The Excel formula bar — type the equals sign, the function name, and Excel shows you the expected arguments as you type.

The four arguments

The lookup_value is what you are searching for. The table_array is the range containing your lookup table — the leftmost column must be the one you are searching. The col_index_num is a number indicating which column of the table to return: 1 returns the first column (the lookup column itself), 2 returns the second, and so on. The range_lookup is almost always FALSE — use FALSE unless you specifically need approximate matching with a sorted table.

Your first VLOOKUP

Using the sample dataset: a list of salespeople with their customer counts, net sales, and profit figures. To find how many net sales John made:

Exact vs approximate matching

FALSE (exact match) — Excel looks for a value that is exactly equal to the lookup value. If the value is not found, it returns #N/A. Use this for names, codes, IDs, and any categorical lookup.

TRUE (approximate match) — Excel finds the largest value that is less than or equal to the lookup value. Only works correctly when the lookup column is sorted in ascending order. Use this for range-based lookups: tax brackets, commission tiers, discount levels based on quantity.

Common errors and how to fix them

#N/A — the lookup value was not found. Check for: trailing spaces (TRIM your data), different capitalisation (both sides should be same case), a number stored as text on one side. Use IFERROR(VLOOKUP(...),"Not found") to display a friendly message instead.

#REF! — the col_index_num is larger than the number of columns in the table_array. Count the columns in your table and ensure the index does not exceed that count.

#VALUE! — usually means the col_index_num is less than 1, or the table_array is not a valid range reference.

Wrong value returned — almost always means the lookup column is not the leftmost column of the table_array. Adjust the table range so the column you are searching is first.

Locking the table reference

When you copy a VLOOKUP formula down a column, the lookup_value should shift (you want each row to look up its own value) but the table_array should not (you always want to search the same table). Use $ signs to lock the table reference: $B$5:$E$17 stays fixed; B5 would shift as you copy.

Select the table range in the formula and press F4 to add the $ signs automatically.

Interactive practice

Download the sample dataset

Practice with the same dataset used in this article — a real VLOOKUP exercise file with sample data, worked examples, and homework questions.

When to use XLOOKUP instead

Excel 365 and Excel 2021 include XLOOKUP — a modernised replacement that can search in any direction (not just left column), returns #N/A with a custom message by default, handles multiple results, and does not require a numbered column index. If your version of Excel supports it, XLOOKUP is preferable. If you are working with older files or sharing with users on Excel 2019 or earlier, VLOOKUP remains the reliable choice.