How to Use VLOOKUP in Excel (With a Working Example)

VLOOKUP searches for a value in the first column of a table and returns a matching value from another column in the same row. The syntax is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), and the part almost everyone gets wrong is the fourth argument, which decides whether Excel finds an exact match or just something close.

The exact syntax#

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

Argument	Required	What it means
lookup_value	Yes	The value you’re searching for. It must exist in the first column of table_array.
table_array	Yes	The full range of cells that contains both the lookup column and the column you want returned.
col_index_num	Yes	The column number inside table_array that holds the value to return, counting the first column as 1.
range_lookup	No	TRUE (or omitted) for an approximate match, FALSE for an exact match.

According to Microsoft’s official VLOOKUP documentation, the lookup_value must always sit in the leftmost column of table_array — VLOOKUP cannot look to its left, only to its right. That single limitation causes more failed lookups than any other part of the formula.

A concrete example#

Say you have a small product list in columns A through C, with SKU codes in column A, product names in column B, and prices in column C, starting at row 2.

Cell	Formula	Result
A2:C10	(source data: SKU, Product, Price)	—
F2	=VLOOKUP("SKU-104", A2:C10, 3, FALSE)	Returns the price in column C for the row where column A equals “SKU-104”
F3	=VLOOKUP(E3, A2:C10, 2, FALSE)	Returns the product name for whatever SKU is typed into cell E3

Here, A2:C10 is the table_array, 3 tells Excel to pull from the third column of that range (Price), and FALSE forces an exact match on the SKU. Lock the range with dollar signs, like $A$2:$C$10, before copying the formula down a column, or the range will shift and start pulling from the wrong rows.

FALSE vs. TRUE: the critical setting#

The fourth argument controls the match type, and getting it wrong is the single most common VLOOKUP mistake.

• FALSE (or 0) — exact match only. Excel returns the value only if it finds lookup_value precisely; otherwise it returns #N/A. Use FALSE for anything that isn’t a sorted numeric range: SKUs, names, IDs, dates.
• TRUE (or 1), or leaving the argument out entirely — approximate match. Excel assumes the first column is sorted in ascending order and returns the closest value that is less than or equal to lookup_value. This is Excel’s default behavior when you skip the argument, and it silently returns wrong data on unsorted lists instead of erroring out.

Per Microsoft’s own explanation, TRUE only works correctly when the first column of table_array is sorted — otherwise “the results might not be what you expect.” In practice, type FALSE every time unless you’re deliberately building a bracket-style lookup (tax brackets, grading scales) on sorted data.

Common #N/A causes and fixes#

Symptom	Likely cause	Fix
#N/A even though the value is visibly there	Numbers stored as text (or vice versa) in either the lookup cell or the source column	Reformat both to the same type, or wrap one side in VALUE() or TEXT()
#N/A on data pasted from another system	Hidden leading/trailing spaces or non-printing characters	Wrap the lookup value in TRIM() or CLEAN()
#N/A when you’re sure the value exists	range_lookup is FALSE and there’s genuinely no exact match, often due to a typo	Double-check spelling, or switch to IFERROR(VLOOKUP(...), "Not found") to handle it gracefully
#N/A after copying the formula down	table_array wasn’t locked with $, so the range shifted and no longer covers the lookup value	Use absolute references, e.g. $A$2:$C$10
#REF! instead of #N/A	col_index_num is larger than the number of columns in table_array	Recount the columns in your range
VLOOKUP returns data from the wrong row	The value you’re searching for isn’t in the leftmost column of table_array	Rearrange the source columns, or use INDEX/MATCH instead

Microsoft’s #N/A troubleshooting guide confirms the two biggest culprits are exact-match lookups with no real match in the data, and formatting mismatches between the lookup value and the source column — both fixable without changing the underlying data.

XLOOKUP: the modern replacement#

If you have Microsoft 365, Microsoft now recommends XLOOKUP instead — it searches in any direction, defaults to exact match instead of approximate, and doesn’t require counting columns, though VLOOKUP still works fine and remains the more widely recognized formula in shared workbooks.

VLOOKUP is worth learning properly because it still shows up in nearly every inherited spreadsheet, template, and job interview, even as XLOOKUP takes over for new work. Start every new VLOOKUP with FALSE typed in as the last argument, lock your table_array with dollar signs before copying, and most of the errors above never happen in the first place. If you’re troubleshooting formulas on a machine that’s also acting up in other ways, it’s worth checking whether Windows itself needs freeing up or whether a stuck program is the real source of the slowdown, covered in our guide on fixing an app that won’t open on Windows.