VLOOKUP Explained: The Only Guide You Need

What VLOOKUP Does in Plain English

Imagine you have a list of products with prices. You know the product name and want to find its price. VLOOKUP says: "Go to the table, find the row where the first column matches the product name I give you, then tell me what is in column 3 of that row."

The "V" stands for "vertical" because it searches down a column. If you need to search across a row, you would use HLOOKUP, but that is rare in practice.

The Syntax

• lookup_value: The value you are searching for (e.g., a product name or employee ID). This must appear in the first column of your table.
• table_array: The range of cells that contains your data. The first column of this range is where VLOOKUP searches.
• col_index_num: Which column number (counting from the left of your table_array) holds the value you want returned. 1 is the first column, 2 is the second, and so on.
• range_lookup: FALSE for an exact match (what you want 99% of the time) or TRUE for an approximate match. If you omit this, Excel assumes TRUE, which causes errors for most use cases. Always specify FALSE.

Step-by-Step Example

Say you have this sales data in cells A1:C6:

You want to find the revenue for "Gadget X." Your formula:

Breaking it down: Search for "Gadget X" in the first column of A2:C6, then return the value from column 3 (Revenue). FALSE means exact match. Result: $6,375.

In practice, you would usually put the lookup value in a cell (say E2) and write =VLOOKUP(E2, A2:C6, 3, FALSE) so the formula updates when you change E2.

Common VLOOKUP Errors and How to Fix Them

#N/A — "Value Not Found"

The most common VLOOKUP error. It means the lookup value does not exist in the first column of your table. Causes:

• Extra spaces in the data. Fix with TRIM() around both the lookup value and the source data.
• Case mismatch — VLOOKUP is not case-sensitive by default, so this is rarely the issue. But if data has invisible characters, paste values to strip formatting.
• Number stored as text. If your lookup is the number 123 but the column contains "123" (text), they will not match. Convert with VALUE() or -- prefix.

#REF! — "Column Index Out of Bounds"

Your col_index_num is larger than the number of columns in your table_array. If your table is 3 columns wide, you cannot ask for column 4. Fix: count your columns carefully or expand the table range.

#VALUE! — "Wrong Data Type"

Usually means the lookup_value or col_index_num is not what Excel expects. Common cause: col_index_num accidentally references a cell containing text instead of a number, or the lookup_value is an array where a single value is expected. Double-check each argument.

VLOOKUP vs XLOOKUP vs INDEX/MATCH

Bottom line: If you have Excel 365 or Google Sheets, use XLOOKUP -- it is VLOOKUP's replacement with fewer gotchas. If you need backward compatibility or maximum flexibility, INDEX/MATCH is the power-user choice. VLOOKUP is still fine for simple, right-direction lookups and remains the most widely understood formula in any office.