VLOOKUP returns #N/A when it cannot find your search value in the first column of the range. Often the value looks like it is there. This page covers the usual causes in the order worth checking, with a formula fix for each, and ends with the case where the value is genuinely missing or just spelled differently.
Master list, D2:E5 (name, ID):
Initech, 101
Umbrella Corp, 102
Acme Pty Ltd, 103
Globex Corporation, 104
Search values go in column A from A2, results in column B. Row 1 holds headers.
"Initech " with a trailing space is a different value from "Initech". Wrap the search value in TRIM:
=VLOOKUP(TRIM(A2),$D$2:$E$5,2,FALSE)
With A2 = "Initech " (trailing space), the plain VLOOKUP gives #N/A and the TRIM version gives 101. To check a cell, use =LEN(A2)<>LEN(TRIM(A2)). TRUE means it has extra spaces.
Text pasted from web pages often contains non-breaking spaces (character 160), which TRIM does not remove. Swap them for normal spaces first:
=VLOOKUP(TRIM(SUBSTITUTE(A2,CHAR(160)," ")),$D$2:$E$5,2,FALSE)
If the spaces are in the master list rather than the search value, TRIM on the search value cannot help. Clean the list in a helper column, F2 filled down, then look up against it:
=TRIM(SUBSTITUTE(D2,CHAR(160)," "))
=INDEX($E$2:$E$5,MATCH(TRIM(A2),$F$2:$F$5,0))
A text "102" and a number 102 look the same but do not match. Take a price list J2:K4 (ID, product) with real numbers: 101 Widget, 102 Gadget, 103 Gizmo. If A2 holds 102 as text (it left-aligns, and =ISTEXT(A2) is TRUE), convert the search value:
=VLOOKUP(VALUE(A2),$J$2:$K$4,2,FALSE)
This returns Gadget. If it is the other way round, with text IDs in the list and a real number in A2, convert the search value to text. Either of these works:
=VLOOKUP(TO_TEXT(A2),$J$2:$K$4,2,FALSE)
=VLOOKUP(A2&"",$J$2:$K$4,2,FALSE)
Imported CSVs, IDs with leading zeros and cells that start with an apostrophe are the usual sources.
The fourth argument controls exact versus approximate matching. If you leave it out, Sheets assumes TRUE (approximate), which expects the first column sorted ascending. On unsorted data it can return #N/A or a wrong row, with no warning. On the four-row sample list above (unsorted), =VLOOKUP("Initech",$D$2:$E$5,2) returned 104, which is Globex's ID, not Initech's 101. For almost every lookup, write FALSE:
=VLOOKUP("Acme Pty Ltd",$D$2:$E$5,2,FALSE)
This returns 103.
VLOOKUP only searches the leftmost column of the range, and can only return from columns to its right. If you search for an ID (column E) and want the name (column D), VLOOKUP over D2:E5 fails. Use INDEX and MATCH, which do not care about order:
=INDEX($D$2:$D$5,MATCH(A2,$E$2:$E$5,0))
With A2 = 103 this returns Acme Pty Ltd. XLOOKUP does the same: =XLOOKUP(A2,$E$2:$E$5,$D$2:$D$5,"Not found").
If you write =VLOOKUP(A2,D2:E5,2,FALSE) and fill down, the range slides down with each row: D3:E6, then D4:E7. Rows drop off the top of your list, and the lookup fails further down. Lock the range with dollar signs (press F4 while editing):
=VLOOKUP(A2,$D$2:$E$5,2,FALSE)
With A2:A5 = Globex Corporation, Acme Pty Ltd, Initech, Umbrella Corp and the unlocked version filled down, you get 104, 103, #N/A, #N/A. The locked version gives 104, 103, 101, 102.
Once the causes above are ruled out, #N/A is the correct answer. To show something cleaner, use IFNA, which catches only #N/A:
=IFNA(VLOOKUP(A2,$D$2:$E$5,2,FALSE),"Not found")
With A2 = Wayne, this shows Not found. Prefer IFNA to IFERROR. IFERROR hides every error, including a mistyped column number, so a real bug looks like a missing value. Use IFERROR(...,"") only once the sheet is finished and checked.
"Inytech" is not "Initech", and "Acme Pty" is not "Acme Pty Ltd". TRIM and exact matching cannot bridge that. If the difference is one string sitting inside another, see our partial-match guide, which uses wildcards: https://www.snakbacks.com/vlookup-partial-match-google-sheets If it is typos, extra words or different word order, formulas will not find it, and you have to match by similarity.
Test the match directly: =A2=D2 returns FALSE if they differ.
Compare lengths with =LEN(A2) and =LEN(D2). A mismatch means hidden spaces.
Compare =ISNUMBER(A2) and =ISNUMBER(D2). A mismatch means text versus number.
Check the FALSE, the $ signs, and which column holds the key.
Disclosure: Snakbacks, the publisher of this site, sells a paid script pack and makes two free add-ons for Google Sheets.
If your cleaned data still gives #N/A because of typos or spelling variants, the Fuzzy Match Template for Google Sheets (US$9, no add-on install): https://snakback.gumroad.com/l/fuzzy-formulas adds FUZZYLOOKUP(values, lookup, return_range, [threshold]) and FUZZYSCORE(a, b), pasted into your sheet once so it works where a Workspace admin blocks add-ons, with a 30-day refund if it does not help. Causes 1 to 6 need nothing but the free formulas above.
Our two free add-ons, Fuzzy Match and Duplicate Remover, are coming soon to the Google Workspace Marketplace.
Snakbacks publishes the Duplicate Remover and Fuzzy Match add-ons.