Fixes / Google Sheets · Checked

Google Sheets "Did not find value in VLOOKUP evaluation": fixes

Short answer

This #N/A message means VLOOKUP searched the first column of your range and found no cell exactly equal to your search key. The most common cause is a mismatch you can't see: extra spaces, or a number stored as text on one side only. Wrap the key in TRIM, make both columns the same format, and set the last argument to FALSE.

What this message means

VLOOKUP(search_key, range, index, [is_sorted]) looks only in the first (leftmost) column of the range. When it finds no match, the cell shows #N/A, and hovering over it shows "Did not find value 'X' in VLOOKUP evaluation", where X is the exact value it searched for. With is_sorted set to FALSE (exact match), the key does not exist in that column, at least not in the same exact form. With is_sorted set to TRUE or left out, you also get it when the key is smaller than the smallest value in the column, or when the column is not sorted. This is a data or formula problem, not a bug in Google Sheets.

Common causes

CauseHow to tell
Extra spaces or invisible characters in the search key or the lookup columnThe value in the tooltip looks identical to a value in the first column. =LEN(A2) gives a higher count than the visible characters, or =A2=D5 returns FALSE for two cells that look the same. This is common with data pasted from websites, PDFs or other systems.
Number stored as text on one side and as a real number on the otherOne column's numbers align left (text) and the other's align right (numbers), or =ISTEXT(A2) returns TRUE on one side only. IDs, ZIP codes, product codes and imported CSV data are the usual culprits.
The search key is not in the first column of the rangeThe value exists in your sheet, but in a column to the right of where the range starts, for example the range is A:C and the IDs are in column B. VLOOKUP cannot search to the left or in the middle.
is_sorted is TRUE or left out while the data is not sortedThe formula has only three arguments or ends in TRUE. Some rows give wrong results or #N/A even though the value is clearly there, and sorting the column changes the results.
The range shifted when you copied or dragged the formula downThe top rows work but lower rows show #N/A. Clicking a failing cell shows a range like A3:C12 instead of A2:C11, so the rows at the top of the table are no longer included.
The value genuinely is not in the lookup tablePressing Ctrl+F (Cmd+F on Mac) and searching for the value in the lookup sheet finds nothing, or finds it only with a typo or different spelling.

How to fix it

1. Use exact match (FALSE as the last argument)

  1. Click the cell with the error and look at the formula in the formula bar.
  2. If it has only three arguments, or ends in TRUE, change the last argument to FALSE, for example =VLOOKUP(A2, Sheet2!A:C, 3, FALSE).
  3. Press Enter and check whether the #N/A clears.
  4. Only use TRUE for range lookups such as tax brackets or grade bands, and in that case sort the first column in ascending order (Data > Sort range).

2. Remove extra spaces and hidden characters

  1. To fix the formula without changing the data, wrap the key: =VLOOKUP(TRIM(A2), Sheet2!A:C, 3, FALSE).
  2. To clean the lookup column itself, select it and choose Data > Data cleanup > Trim whitespace.
  3. If the data came from a web page, it may contain non-breaking spaces that TRIM does not remove. Use =VLOOKUP(TRIM(SUBSTITUTE(A2, CHAR(160), " ")), Sheet2!A:C, 3, FALSE).
  4. For tabs or line breaks, use TRIM(CLEAN(A2)) as the search key.
  5. Check that it worked: =LEN(A2) should now match the number of visible characters.

3. Match text and number formats

  1. Check both sides with =ISNUMBER(A2) on the search key and =ISNUMBER(Sheet2!A2) on the first lookup column.
  2. If the key is text but the lookup column holds numbers, use =VLOOKUP(VALUE(A2), Sheet2!A:C, 3, FALSE).
  3. If the key is a number but the lookup column holds text, use =VLOOKUP(A2&"", Sheet2!A:C, 3, FALSE) or TO_TEXT(A2).
  4. To fix it for good, select both columns and choose Format > Number > Number (or Format > Number > Plain text for codes with leading zeros), then re-enter any values that stay left-aligned.

4. Lock the range so it doesn't shift when copied

  1. Click the first formula cell and add $ signs to the range, for example change A2:C100 to $A$2:$C$100.
  2. Or use whole columns, such as Sheet2!$A:$C, so new rows are included automatically.
  3. Drag or copy the formula down again.
  4. Click one of the lower cells and confirm the range is identical to the one in the first row.

5. Make the lookup column the first column (or use XLOOKUP)

  1. Find which column holds the values you are searching for.
  2. Start the range at that column, for example if IDs are in column B and you want column D, use =VLOOKUP(A2, Sheet2!B:D, 3, FALSE). The index counts from the first column of the range, so column D is now 3.
  3. If the column you want to return is to the left of the lookup column, use XLOOKUP instead: =XLOOKUP(A2, Sheet2!B:B, Sheet2!A:A).
  4. INDEX and MATCH also work: =INDEX(Sheet2!A:A, MATCH(A2, Sheet2!B:B, 0)).

6. Show a blank or custom message for values that really are missing

  1. First confirm the value really isn't in the table (Ctrl+F or Cmd+F on the lookup sheet).
  2. Wrap the formula in IFNA: =IFNA(VLOOKUP(A2, Sheet2!A:C, 3, FALSE), "Not found").
  3. To show an empty cell instead, use "" as the second argument.
  4. Use IFNA rather than IFERROR so other problems, such as a wrong index number (#REF!), still show up.

Next time, skip the search

Crade is an AI assistant for Mac and Windows that sees your screen. When a message like this pops up, ask “what does this mean?” and it explains the exact error in front of you, step by step.

Download Crade

Still not fixed?

Test in isolation: in an empty cell, enter =A2=Sheet2!A5 (your search key against the cell that should match). If it returns FALSE, the two values differ, so compare =LEN() and =CODE(LEFT(A2)) on both to find the hidden difference. If it returns TRUE but VLOOKUP still fails, check the range and is_sorted argument again. If the lookup data comes from IMPORTRANGE, open the source sheet once and click Allow access. If the data comes from a form, database export or another system, ask whoever manages that system to export IDs in one format. You can also post a copy of the sheet (with sensitive data removed) in the Google Docs Editors Community.

Questions people ask

Why does VLOOKUP say it did not find a value that is clearly in my sheet?

Almost always, the two values differ in a way you can't see: a trailing space, a non-breaking space, or a number stored as text on one side. Another cause is that the value sits in a column other than the first column of your range.

How do I make VLOOKUP return blank instead of #N/A?

Wrap it in IFNA: =IFNA(VLOOKUP(A2, B:C, 2, FALSE), ""). This hides only the not-found error and leaves other errors visible.

Should the last VLOOKUP argument be TRUE or FALSE?

Use FALSE for exact matches such as names, IDs and codes. If you leave the argument out, Google Sheets uses TRUE (approximate match), which needs the first column sorted and can return #N/A or wrong results.

Can VLOOKUP look to the left in Google Sheets?

No. VLOOKUP only searches the first column of the range and returns values to its right. Use XLOOKUP or INDEX/MATCH to return a value from a column to the left.

Is VLOOKUP case-sensitive in Google Sheets?

No. "abc" and "ABC" match, so case differences don't cause this error. Look for spaces or format mismatches instead.

Sources

Related errors