VLOOKUP not working: #N/A, #REF!, #VALUE! or wrong value

#N/A      – no match found (or approximate match below the smallest value)
#REF!     – col_index_num is greater than the number of columns in table_array
#VALUE!   – col_index_num is text or less than 1, or lookup_value is over 255 characters
#NAME?    – text lookup value typed without quotes
(no error, wrong row returned) – range_lookup is TRUE or omitted on unsorted data

Why it happens

The VLOOKUP function has this syntax in Excel:

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

Each argument has a rule, and each broken rule produces a specific symptom. Microsoft's documentation lists them:

• range_lookup defaults to approximate match. If you leave the fourth argument out, VLOOKUP uses TRUE, which "assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value". If the first column is not sorted, "the return value might be something you don't expect". With TRUE, a lookup value smaller than the smallest value in the first column also returns #N/A.
• The lookup value must be in the first column of table_array. If your range is B2:D7, VLOOKUP only searches column B. It cannot look to its left.
• col_index_num counts from the first column of the range, starting at 1. If it is greater than the number of columns in table_array, you get #REF!. The #VALUE! article for VLOOKUP adds that a column index that contains text or is less than 1 returns #VALUE!, which can happen when a nested function returns 0.
• lookup_value longer than 255 characters returns #VALUE!.
• Types and hidden characters. The general #N/A troubleshooting page lists "incorrect value types" (you look up a number but the source data is stored as text) and extra leading or trailing spaces as common causes. The VLOOKUP page adds inconsistent straight and curly quotation marks and non-printing characters.
• Relative references slide when you fill down. If the range is written as A2:C100 and you copy the formula down one row, it becomes A3:C101, so the lower rows search a range that no longer contains the top of the table. Microsoft's best-practice table says to use absolute references so the formula "always looks at the same exact lookup range".

How to fix it

Work through these in order. Each step targets one of the causes above.

1. Force an exact match. Add FALSE (or 0) as the fourth argument. This is the fix for a VLOOKUP that returns a wrong value from another row.

   =VLOOKUP(D2, A2:C100, 3, FALSE)
   

   Only use TRUE deliberately, for banded lookups such as tax brackets or grade boundaries, and then sort the first column in ascending order first.

2. Start the range at the lookup column. If the IDs you search for are in column B and the result is in column D, the range must start at B, and the result is column 3 of that range:

   =VLOOKUP(G2, B2:D100, 3, FALSE)
   

   If the value you want to return is to the left of the lookup column, VLOOKUP cannot do it. Use XLOOKUP instead (see below).

3. Check the column number. Count the columns of table_array, not of the sheet. In B2:D100, B is 1, C is 2, D is 3; any number above 3 returns #REF!, and 0 or text returns #VALUE!.

4. Lock the table range before filling down. Add $ signs, or select the range reference in the formula bar and press F4, as described in Use absolute references:

   =VLOOKUP(D2, $A$2:$C$100, 3, FALSE)
   

   D2 stays relative so each row looks up its own value; only the table is fixed.

5. Make the data types match. If the lookup cell holds the number 1024 but the table holds the text "1024" (or the other way round), they do not match. Microsoft's fix is to select the cells, press Ctrl+1 (Format Cells > Number) and apply the same format to both. To force a whole column to convert after changing its format, Microsoft suggests Data > Text to Columns > Finish. Do this on the first column of the table and on the lookup values.

6. Remove leading and trailing spaces. Microsoft's #N/A article gives this formula, which trims the names in A2:A7 inside the lookup:

   =VLOOKUP(D2, TRIM(A2:B7), 2, FALSE)
   

   In Excel for Microsoft 365, press Enter. In older versions this is an array formula: confirm it with Ctrl+Shift+Enter. A more durable fix is to clean the source column once with TRIM in a helper column and paste the results as values. Note that TRIM removes the ordinary space (character 32) but not the non-breaking space (character 160) that often comes from web pages.

7. Shorten lookup values over 255 characters, or switch to INDEX and MATCH. Microsoft's workaround for long text is an INDEX/MATCH combination entered as an array formula. One way to write it, comparing the whole column to the lookup text:

   =INDEX(B2:B100, MATCH(TRUE, A2:A100=D2, 0))
   

   In Excel for Microsoft 365, press Enter; in older versions, confirm with Ctrl+Shift+Enter.

8. Hide a genuine "not found" only after the steps above. If the value really is not in the table, #N/A is the correct answer. To show something else, wrap the formula:

   =IFERROR(VLOOKUP(D2, $A$2:$C$100, 3, FALSE), "Not found")
   

   Do not use this to hide errors you have not diagnosed: IFERROR also hides #REF! and #VALUE!, which point to a broken formula, not a missing value.

Check that it worked

• Pick one row where you know the answer and compare the VLOOKUP result with the source row by eye. An approximate-match bug often returns a plausible value from another row.
• Fill the formula down and click a cell near the bottom. In the formula bar, the table reference should still read $A$2:$C$100, not a shifted range.
• To test whether two values that look identical really are, enter =D2=A5 in a spare cell (using the lookup cell and the table cell you expect to match). FALSE means they differ in type or in hidden characters, so step 5 or 6 still applies.
• Count the #N/A results that remain. Each one should correspond to a key that is genuinely absent from the table.

VLOOKUP over a whole column returns #SPILL!

In current versions of Excel, a formula such as =VLOOKUP(A:A, A:C, 2, FALSE) passes a whole column as the lookup value. Microsoft's VLOOKUP page explains that this relies on implicit intersection and can return #SPILL!. Either anchor it with the @ operator, =VLOOKUP(@A:A, A:C, 2, FALSE), or go back to a single cell, =VLOOKUP(A2, A:C, 2, FALSE). If the error persists, the #SPILL! error guide covers the other reasons a formula cannot spill.

The same problems in Google Sheets

Google Sheets uses the same structure, =VLOOKUP(search_key, range, index, [is_sorted]), and the Google Docs Editors Help page describes the same failure modes: is_sorted is TRUE by default and gives a wrong value on unsorted data, #REF! comes from an index larger than the range's column count, and #VALUE! from an index that is text or below 1. Google recommends always writing FALSE explicitly. To strip spaces, Sheets has Data > Data cleanup > Trim whitespace; to fix numbers stored as text, select the column and use Format > Number. To replace only the not-found case, Google suggests IFNA:

=IFNA(VLOOKUP(G3, B4:D8, 3, FALSE), "NOT FOUND")

If your lookup table comes from another spreadsheet through IMPORTRANGE and the cell shows "You need to connect these sheets", VLOOKUP has nothing to search yet. Fix the connection first with the IMPORTRANGE access guide.

When to switch to XLOOKUP

Microsoft's own VLOOKUP page recommends XLOOKUP where it is available: it takes separate lookup and return ranges, so it can return a value from a column to the left, and its match_mode defaults to an exact match, which removes the most common VLOOKUP mistake. The equivalent of step 1 is:

=XLOOKUP(D2, $A$2:$A$100, $C$2:$C$100, "Not found")

XLOOKUP does not fix data problems, though: a number stored as text or a trailing space still produces #N/A. If you have already switched and see that, read why XLOOKUP returns #N/A when the value exists.

Most VLOOKUP failures come from a default you did not choose rather than a broken function: approximate matching, a range that starts in the wrong column, or a reference that moved when you filled down. Setting the last argument to FALSE, locking the range with $ and checking that the lookup value and the table hold the same type fixes the causes listed above; whatever #N/A remains after that is usually a key that really is missing from the table.