VLOOKUP Explained: Syntax, 4 Arguments and Why You Get #N/A

The syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

VLOOKUP looks up a value in a table by row. Microsoft documents it as VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The first three arguments are required. Only range_lookup, in square brackets, is optional.

It works in Excel for Microsoft 365, Excel 2024, 2021, 2019 and 2016.

What each of the 4 arguments does

Each argument has one job. Get all 4 right and the formula works the first time.

• lookup_value (required): the value to find. It must sit in the first column of table_array and can be a typed value or a cell reference such as D2.
• table_array (required): the range to search. It must include the column with the lookup value and the column you want back. Named ranges and Excel tables are allowed.
• col_index_num (required): the number of the column to return from. The leftmost column of table_array is 1, the next is 2, and so on.
• range_lookup (optional): 1 or TRUE for approximate match, 0 or FALSE for exact match. If you leave it out, Excel uses approximate match (TRUE).

TRUE or FALSE: the omitted fourth argument is the usual cause of wrong answers

FALSE (or 0) searches the first column for the exact value. Microsoft's own example is =VLOOKUP("Smith",A1:B100,2,FALSE). If Excel does not find Smith, it returns #N/A.

TRUE (or 1, or leaving the argument out) finds the closest match. Microsoft states that this mode assumes the first column is sorted numerically or alphabetically. If the column is not sorted, the result may be unexpected, so you can get a wrong value with no error at all.

Microsoft's #N/A article warns that TRUE can return #N/A and also wrong results, and its own Pear example returned an incorrect price. With TRUE, you get #N/A when the lookup value is smaller than the smallest value in the first column.

A worked example with a 4-row price list

Say cells A1:C5 hold a price list: row 1 is the header, rows 2 to 5 hold IDs in A, names in B, prices in C. These sample values are invented.

A2: 101, Pen, 1.20. A3: 102, Notebook, 3.50. A4: 105, Stapler, 7.25. A5: 110, Tape, 2.10. The IDs are sorted from low to high.

To get the price of product 105, type =VLOOKUP(105,A2:C5,3,FALSE). Column C is the third column of A2:C5, so the formula returns 7.25. Change the 3 to 2 and it returns Stapler, because B is column 2.

Now look up 107, which is not in the list. =VLOOKUP(107,A2:C5,3,FALSE) returns #N/A, because FALSE needs 107 to be there. =VLOOKUP(107,A2:C5,3,TRUE) returns 7.25, because 105 is the closest value that is not larger than 107. That is useful for a band table and a trap for an ID list.

VLOOKUP only looks right, and the lookup column must come first

The lookup value must be in the first column of table_array, and the return column must be inside the range, to its right. If the price list had the names in column A and the IDs in column B, a VLOOKUP on the ID could not return the name, because the ID is not the first column of the range.

The usual fixes are to move the columns, or to use INDEX and MATCH. Microsoft also points to INDEX and MATCH as the workaround for long lookup values. The newer XLOOKUP function can look in either direction, but it is not in Excel 2016 or Excel 2019, and another page here covers it.

Lock the table with dollar signs, as in $A$2:$C$5, when you copy the formula down a column, so the range does not shift.

Why VLOOKUP returns #N/A, even when the value is in the table

#N/A means Excel did not find a match. Microsoft lists four causes in its VLOOKUP-specific troubleshooting.

First, the value really is missing. Add it to the table, or wrap the formula: =IFERROR(VLOOKUP(D2,$D$6:$E$8,2,FALSE),0) shows 0 instead of the error. You can show "" or your own message instead of 0.

If you are looking at an error right now, Crade can read your screen and point to which of these is wrong in your sheet.

• Different data types: a number in the lookup cell and the same number stored as text in the table do not match. Select the cells, press Ctrl+1 and check the Number format.
• Extra spaces: a trailing space makes "Pen" and "Pen " different. Microsoft shows =VLOOKUP(D2,TRIM(A2:B7),2,FALSE), which needs Ctrl+Shift+Enter in versions before dynamic arrays.
• Approximate match: with TRUE on unsorted data, the answer can be #N/A or a wrong value. Set the fourth argument to FALSE.
• Missing arguments: check that all 3 required arguments are in the formula.

#REF! means the column number is too big

#REF! appears when col_index_num is higher than the number of columns in table_array. Microsoft's example is =VLOOKUP(A8,A2:D5,5,FALSE). The range has 4 columns, so there is no fifth column and 5 causes #REF!.

There are two fixes. Widen the range to A2:E5 so that column 5 exists, or lower the index to 4 so it falls inside A2:D5. In the price list above, =VLOOKUP(105,A2:C5,4,FALSE) returns #REF!, and changing 4 to 3 fixes it.

#VALUE! has two causes: a lookup value over 255 characters, or an index below 1

Microsoft's #VALUE! article lists two causes for VLOOKUP. The first is a lookup_value longer than 255 characters. Shorten it, or use INDEX and MATCH, which is an array formula: press Enter in Microsoft 365 and Ctrl+Shift+Enter in older versions.

The second is a col_index_num that is text or less than 1, from a typo or a nested function returning 0. So =VLOOKUP(105,A2:C5,0,FALSE) returns #VALUE!.

Who VLOOKUP suits and who should look elsewhere

VLOOKUP suits a simple lookup where the key is in the leftmost column: an ID to a name, a code to a price, a name to an email address. It also suits anyone who must keep a file working in Excel 2016 or 2019, where XLOOKUP is not available.

It suits a sorted band table with TRUE, such as a score to a grade. It does not suit a table where the key sits to the right of the answer, or one that has long text keys above 255 characters. Use INDEX and MATCH, or XLOOKUP, for those.