VLOOKUP in Excel returns #N/A when it cannot find the lookup value in the first column of its search range. With the lookup value 104 in A2 and a table in D2:E100 where ID 104 maps to Widget, use =VLOOKUP(A2,$D$2:$E$100,2,FALSE); it returns Widget if that exact ID is present.
The error is often a sign of a mismatch between the value you are searching for and the values in the table, not a broken workbook. Check the search column, match type, data format, and spaces before changing the formula or hiding the error.
What VLOOKUP searches
VLOOKUP searches vertically: it looks for a value in the leftmost column of a selected table range, then returns a value from another column in the same row. In the example above, column D contains IDs and column E contains names. The 2 in the formula means “return the second column of the selected range,” not worksheet column B or E.
The final argument controls how Excel matches the key. FALSE asks for an exact match, which is the usual choice for product codes, names, invoice numbers, and other identifiers. TRUE, or leaving that argument out, requests an approximate match. Approximate matching assumes the first column is sorted in ascending order; otherwise, it can produce an unexpected result. It can also return #N/A when the lookup value is smaller than the first value in the sorted list.
VLOOKUP only searches the first column of its range and returns data from columns to its right. If you select C:E but the key is actually in column D, VLOOKUP will search column C instead. And if the information you need is to the left of the key, VLOOKUP cannot retrieve it with that range arrangement.
Check the lookup range and match argument
Start by reading the formula from left to right. Confirm that the first argument points to the intended lookup value, the second argument starts with the column containing the keys, and the last argument is FALSE for an exact match. A reference that begins one column too early is a common reason Excel reports that a valid value is missing.
Next, verify the table range. If the formula refers to D2:E50 but the matching record is on row 75, Excel cannot find it. When copying a formula down, use absolute references for a fixed lookup table so the range does not shift. The dollar signs in the example formula hold the range at D2:E100 as the formula is copied. They do not prevent the table from growing; extend the range if new records are added outside it.
Also check that the column number is appropriate. A column number larger than the number of columns in the range typically produces #REF!, not #N/A, but a wrong number that still falls within the range may return data from the wrong column. Count columns from the left edge of the selected range, not from the worksheet’s A column.
Look for differences in data type
Two cells can look identical while Excel stores them differently. For instance, one cell may contain the number 104 and another may contain the text “104.” An exact lookup can fail when the lookup value and table key use different types. This often happens when data is imported from a text file, copied from a website, or entered with an apostrophe before a number.
Check a few cells on both sides of the lookup. Number formats can help reveal a difference, but changing the display format alone does not necessarily convert the underlying value. If IDs may contain leading zeros, such as 00104, keep them as text consistently; converting them to numbers would remove meaningful zeros. For ordinary numeric quantities, convert both the lookup value and the key column to numbers using a consistent method, such as Excel’s Convert to Number option when it appears, or Text to Columns where appropriate.
Do not convert every key to a number without considering what it represents. A part number, postal code, or account code is usually an identifier rather than a quantity. Treating it as text can preserve leading zeros and prevent Excel from applying arithmetic assumptions to it.
Remove spaces and invisible characters
Leading or trailing spaces are easy to miss: “North” and “North ” are different text strings for an exact lookup. Imported data may also contain nonbreaking spaces, line breaks, or other characters that look blank but are not removed by ordinary visual inspection. Compare the source cells carefully, and consider using a temporary helper column to clean the key values on both the lookup side and the table side.
With raw text in A2 containing ordinary spaces around the word North, entering =TRIM(A2) in a helper cell returns North. TRIM removes leading and trailing spaces and reduces repeated internal spaces to one. However, TRIM alone does not remove every nonprinting character or the nonbreaking space that can come from web pages. If the cleaned lookup value still fails, clean the table’s key column as well and compare the results; cleaning only A2 will not fix dirty keys in the lookup table.
Keep the original imported data until you have checked the cleaned results. A helper column makes it easier to see what changed and avoids overwriting source values before you know whether the cleanup is safe.
Use a reliable troubleshooting sequence
Work through these checks in order, testing the formula again after each correction. This makes it easier to identify the underlying cause instead of masking several issues at once.
- Click the cell showing #N/A and inspect the formula bar. Confirm the lookup value and the table range are the ones you intended.
- Locate the key column in the selected range. It must be the range’s first column for VLOOKUP.
- Use FALSE as the final argument unless you deliberately need an approximate lookup and have sorted the key column appropriately.
- Search the key column for the value. Check whether the row falls outside the formula’s range or whether a filter or sort has led you to inspect the wrong record.
- Compare the lookup value and a likely matching key for data type, leading zeros, spaces, and hidden characters.
- Check whether copied formulas have shifted their ranges. Lock a fixed range with dollar signs or use a table reference that expands as records are added.
If you need to confirm whether Excel can find an exact match independently of the return formula, the MATCH function can help. With the key in A2 and the candidate IDs in D2:D100, =MATCH(A2,D2:D100,0) returns the key’s position within that range; if ID 104 is the third entry, the result is 3. If it returns #N/A, focus on the key values or the range rather than the VLOOKUP return-column number.
Show a message without hiding useful errors
Once you understand why a value is missing, you may want the sheet to display a clearer message. With A2 as the lookup key and D2:E100 as the ID-and-name table, =IFNA(VLOOKUP(A2,$D$2:$E$100,2,FALSE),"Not found") returns Not found when the exact key is absent; if 104 is present and maps to Widget, it returns Widget. IFNA handles #N/A specifically, leaving other errors visible for investigation.
That distinction matters. IFERROR can replace many kinds of errors, including errors that may point to a faulty range or column number. Using a broad error handler can make a workbook look clean while concealing a real formula problem. Use IFNA when a missing match is an expected possibility, and make the fallback text appropriate to the task. In a report, “Not found” may be clearer than a blank; in a data-entry sheet, a blank might be preferable if users understand what it means.
A fallback message does not repair the lookup. If the key should be present, treat the message as a prompt to investigate rather than accepting it as a correct result.
VLOOKUP alternatives in Microsoft 365
For new formulas, XLOOKUP can be easier to read because it takes separate lookup and return ranges, uses exact matching by default, and can return a custom message when there is no match. It can also return values from either side of the lookup column. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; it is not available in some older perpetual versions.
With the lookup value in A2, IDs in D2:D100, and names in E2:E100, =XLOOKUP(A2,D2:D100,E2:E100,"Not found") returns Widget when ID 104 is found and Not found when no exact match exists. Because the lookup and result ranges are specified separately, there is no VLOOKUP-style column index to count. The formula still cannot resolve genuinely inconsistent keys, so type and character cleanup remains important.
VLOOKUP remains useful in existing workbooks and in files that need to work with older Excel versions. Another option is INDEX with MATCH, which can look to the left as well as the right, but it is more complex to read for many users. Choose an approach based on the workbook’s version requirements and the people who will maintain it.
Frequently Asked Questions
Why does VLOOKUP return #N/A even though the value is visible?
The visible values may differ underneath: one could be text and the other a number, or one may contain a trailing space or hidden character. Also confirm that VLOOKUP is searching the column where the visible key actually appears and that the key is inside the selected range.
Does FALSE mean the lookup is not working?
No. FALSE tells VLOOKUP to require an exact match, which is normally what you want for identifiers. If no identical value exists in the first column of the range, #N/A is the expected result. TRUE is for approximate matching and requires sorted lookup data.
Can VLOOKUP search to the left?
No. VLOOKUP searches the leftmost column of its table range and returns a value from a column to its right. Use XLOOKUP in a supported Excel version, or consider INDEX and MATCH, when the return column is to the left.
Should I replace every #N/A with a blank?
Not automatically. A missing match may be a legitimate outcome, but it may also reveal a misspelled key, an incomplete range, or inconsistent data. Use IFNA only when a fallback is appropriate, and choose a message that does not make missing data look like a confirmed result.
Why does the error appear only after I copy the formula?
The lookup range may have moved as the formula was filled down. Lock a fixed range with dollar signs, as in $D$2:$E$100, or use a suitably defined Excel table so the lookup source can include added rows. Check a copied formula in the formula bar to see its actual references.
Prevent future lookup errors
Reliable lookups start with consistent keys and a well-defined source table. Keep identifier columns in one consistent data type, avoid stray spaces, and make sure the lookup range includes all records. Use exact matching for ordinary key searches, lock ranges when copying formulas, and review unexpected error messages rather than hiding them immediately.
When a workbook is shared, document whether IDs are text or numbers and what a missing result means. That small bit of context helps the next person distinguish a real absent record from an accidental formatting mismatch—and makes the next #N/A much faster to diagnose.
Related
- Using ISBLANK in Excel: Check for Truly Empty Cells
- Use Power Query to Merge Excel Files: A Practical Guide
- Update Number Format in Excel: How to Change Formats
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Error 0x426-0x0: What It Means and How to Fix ItExcel error 0x426-0x0 is usually an Office desktop startup problem, often involving the Microsoft Office Click-to-Run service that…
- Excel Error: Why You Can’t Change Part of an ArrayThe Excel message “You can’t change part of an array” means the cell you are trying to edit…
- Excel TRANSPOSE Not Working? Fix the Common CausesIn Excel for Microsoft 365, enter =TRANSPOSE(A2:C4) in one empty cell and make sure the cells where the…
- Excel Hyperlink to Another Sheet Not Working? FixesIf an Excel hyperlink to another sheet is not working, check that the destination sheet exists and is…
- Excel Not Grouping Rows? Fix Outlines and Data IssuesIf Excel is not grouping rows, select the rows or cells you want to outline and choose Data…
- XLOOKUP Not Working Across Workbooks? Fix the LinksYes—XLOOKUP can look up a value in another Excel workbook. For the most reliable results, open both workbooks,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.