To fix a #N/A error in Excel VLOOKUP, first make sure the formula searches the correct first column, then use FALSE for an exact match and check that the lookup value matches the source data exactly. Hidden spaces, text-versus-number differences, or a missing lookup value can also cause the error. You will need the workbook and permission to inspect or edit the lookup table; make a copy first if the data is important.
What You’ll Need
- The worksheet containing the VLOOKUP formula and its source table.
- The value you expect the formula to find, plus the column where that value should appear.
- Permission to adjust the formula or clean the source data. If the workbook is shared or protected, you may need the owner to make changes.
Step-by-Step
- Read the formula and identify its four parts. Click the cell showing #N/A and inspect the formula bar. A VLOOKUP has the form lookup value, table array, column index, and range lookup. With employee names in A2:A100, departments in B2:B100, salaries in C2:C100, and the name to find in E2, the formula to return a salary is
=VLOOKUP(E2,$A$2:$C$100,3,FALSE). In this layout, the formula returns the salary from column C for the exact name in E2. The dollar signs keep the lookup range fixed if you copy the formula down. Before changing anything, check that the formula points to the intended cells and that the column index is within the selected range. - Set the match type to exact. The fourth argument should normally be FALSE when looking up names, product codes, IDs, or other values that must match precisely. If you leave this argument out, VLOOKUP uses approximate matching by default. Approximate matching is intended for sorted ranges and threshold lookups; it can return a misleading result, or #N/A if the lookup value is below the smallest item in the first column. With the employee layout above, use
=VLOOKUP(E2,$A$2:$C$100,3,FALSE). FALSE tells Excel to look for an exact match rather than the nearest value. Recalculate the cell after making the change and see whether the error disappears. - Confirm the lookup column is the first column of the selected range. VLOOKUP searches only the leftmost column of its table array. If names are in column B but the selected array starts at A, the function will search A and may not find the name. Select a range that begins with the lookup field and includes the result column. For example, if employee names are in B2:B100, departments in C, and salaries in D, with the name in E2, use
=VLOOKUP(E2,$B$2:$D$100,3,FALSE). Here the third column of the selected range is worksheet column D, so the formula returns the salary. Do not assume that a column index refers to the worksheet’s letter; it counts from the left edge of the table array. - Check whether the lookup value actually exists. Compare the lookup value with the source column. Use Ctrl+F on Windows (Command+F on Mac) to search the source list, or filter the column and inspect likely matches. Look for misspellings, a missing row, a different punctuation mark, or an outdated code. You can also copy a source value and paste it into the lookup cell as a quick test. If VLOOKUP then works, the original input differed from the stored value. If the item is genuinely absent, add it to the source table only if it belongs there; otherwise, a not-found result is accurate.
- Look for leading, trailing, or repeated spaces. Text that looks identical on screen may contain extra spaces, tabs, or nonbreaking spaces copied from a website. Click each cell and inspect the formula bar, or compare the length of the lookup text and a suspected matching source value. For cleanup, with the original source text in A2, enter the following in a spare helper column and fill it down:
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))). This removes ordinary extra spaces, many nonprinting characters, and the common nonbreaking space represented by CHAR(160). If the lookup value is in E2, clean it using the same approach in another helper cell, then point VLOOKUP to the cleaned lookup value and cleaned source column. Keep the original values until you have confirmed the cleaned results. - Make sure numbers and text are stored in the same way. A cell containing the text “0042” may not match a numeric 42, and identifiers with leading zeros should usually remain text. Check alignment and warning indicators, but do not rely on appearance alone. With a text-form number in A2, you can convert it to a number in a helper cell using
=VALUE(A2); for the value “42”, the result is the number 42. Use this only if the data represents a number and leading zeros are not meaningful. If the source values are IDs, standardize both sides as text instead, preserving zeros. Convert the whole lookup column and lookup key consistently rather than changing just one cell. - Check the range and column index. Make sure the table array includes every relevant row and the column that contains the result. A fixed range such as $A$2:$C$100 will not include a new record entered in row 101. Expand the range or use an Excel Table so added rows are included. Also verify that the column index is a positive number no greater than the number of columns in the array. A bad column index more commonly causes #REF! than #N/A, but reviewing it prevents fixing the wrong issue. If you use a table, select the table columns carefully and verify that the lookup field remains the leftmost column in the selected range.
- Show a helpful message only after checking the cause. Once the lookup works, you may choose to replace a genuine not-found result with readable text. With the employee data in A2:C100 and the lookup name in E2, use
=IFNA(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found"). If the name exists, this returns the salary; if there is no exact match, it displays Not found. IFNA hides only #N/A, so other errors remain visible. Do not use this wrapper as a substitute for fixing a wrong range, inconsistent data, or a missing record; a blank-looking or friendly result can conceal a problem.
Common Mistakes
A frequent mistake is omitting the fourth VLOOKUP argument and assuming Excel will choose an exact match. It will not: the default is approximate match. Another is selecting a table range whose first column is not the column containing the lookup key. VLOOKUP cannot search to the left within its selected range, so rearrange the range or use a different lookup approach if the result is to the left.
People also treat every #N/A as a formula defect. Sometimes it correctly means the requested item is absent. Before adding a workaround, confirm the source list is complete and the input is spelled as intended. Avoid changing valid IDs, removing meaningful leading zeros, or sorting data just to make an approximate lookup appear to work.
Finally, do not wrap the formula in IFERROR immediately and move on. IFERROR can hide errors other than #N/A, such as a bad column reference or a calculation problem in a dependent formula. IFNA is narrower when the only error you intend to handle is a missing match, but neither function repairs the underlying lookup.
Tips
- Use FALSE explicitly for exact lookups, even when you believe the formula’s default behavior is suitable. It makes the intended match rule visible and reduces surprises when someone edits the formula.
- Convert a source range to an Excel Table with Insert > Table when the list will grow. Formulas that use the table’s columns can expand with new rows, although you still need to ensure the lookup column is first in the VLOOKUP array.
- Keep a clean helper column when imported data contains inconsistent spaces or characters. It is safer to review normalized values before replacing the original data, and it makes repeated lookups more dependable.
- When copying a formula down, use absolute references for a fixed source range, such as $A$2:$C$100, while leaving the lookup cell relative if each row should use its own key.
- If you have Microsoft 365 or Excel 2021, XLOOKUP can search in either direction and lets you specify a not-found result. Older Excel versions may not include XLOOKUP, so VLOOKUP remains useful for compatible workbooks.
Troubleshooting
The formula still shows #N/A after adding FALSE. Check that the lookup value is in the first column of the range and exists there exactly. Test a copied source value in the lookup cell. If that succeeds, compare the original input for spaces, punctuation, or a number/text mismatch. If it still fails, inspect the formula bar for a misplaced range or a reference to a different sheet.
The values look identical but do not match. Use a helper column to remove extra spaces and nonprinting characters, including nonbreaking spaces from copied web content. Clean both the source keys and the lookup value in the same way. If one side contains a true numeric value and the other is text, convert both to the same data type. For codes where zeros or formatting matter, preserve them as text rather than coercing them into numbers.
Some rows work and others return #N/A. That often points to inconsistent entries or a source range that ends before all records. Check a failed key against the source column, then confirm the range covers the full list. If the formula is copied down, make sure the source range is locked with dollar signs, while the lookup reference changes from row to row as intended.
Excel displays a result, but it is the wrong one. Verify that the fourth argument is FALSE. Approximate matching can return a nearby value rather than an exact one. Then check the column index and the order of columns in the selected array. When an exact lookup is required, sorting is not a replacement for specifying FALSE.
The formula appears as text or does not recalculate. If the cell displays the formula itself, set its format to General, edit the cell, and press Enter; also check that the formula does not begin with an apostrophe. If the formula is present but its value seems stale, use Formulas > Calculation Options > Automatic in Excel for Microsoft 365 on Windows. Excel for Mac has the Calculation options under Formulas as well. In Excel for the web, check the formula and references in the browser, and confirm the workbook has finished calculating.
Frequently Asked Questions
What does #N/A mean in VLOOKUP? It means Excel could not find a match for the lookup value under the formula’s matching rules. The item may be missing, the formula may search the wrong column, or the values may differ because of spaces or data types.
Why does VLOOKUP return #N/A when the value is visibly in the table? The visible text may contain hidden spaces or characters, or one value may be stored as text while the other is numeric. Also make sure the value is in the first column of the selected table array, not simply somewhere in the overall table.
Should I use TRUE or FALSE in VLOOKUP? Use FALSE for an exact match, which is the right choice for most names, IDs, and product codes. TRUE or an omitted fourth argument uses approximate matching and is appropriate only for deliberate range or threshold lookups with correctly sorted data.
Can I replace #N/A with a blank? Yes. With the employee list in A2:C100 and the lookup name in E2, use =IFNA(VLOOKUP(E2,$A$2:$C$100,3,FALSE),""). It returns the salary for a match and a blank string when there is no match. Use this only if a blank is a clear and acceptable way to show a missing result; it can make missing data less noticeable.
Will IFERROR fix a VLOOKUP #N/A? IFERROR can display alternate text instead of the error, but it does not make the lookup find a value. IFNA is usually more appropriate when you want to handle only a missing match, since it leaves other errors visible for investigation.
Does this work the same in Excel for Mac or the web? The VLOOKUP formula and its arguments work the same in Microsoft 365 for Windows, Mac, and the web. Ribbon locations and some keyboard shortcuts differ, but the main checks—exact match, correct first column, consistent data, and a complete range—are the same.
Related
- How to Enable Copy and Paste in Excel When It Stops
- How to Enable Autofit in Excel for Columns and Rows
- How to Email an Excel File That’s Too Large Safely
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- XLOOKUP Name Error: Causes, Checks, and Fixes in ExcelIf XLOOKUP returns #NAME?, Excel usually does not recognize something in the formula: the function name may be…
- Excel Unique Name Error: Why It Appears and How to Fix ItIf Excel says a name is already taken or must be unique, the name you are trying to…
- Excel VLOOKUP Not Working? Fix the Common CausesIf Excel VLOOKUP is returning the wrong result or an error, first set its final argument to FALSE…
- Hyperlink Not Working in Outlook? Fixes That HelpIf a hyperlink is not working in Outlook, first check whether Outlook is waiting for a Ctrl+click, then…
- Fix Conditional Formatting in Excel: A Practical GuideTo fix conditional formatting in Excel, open Home > Conditional Formatting > Manage Rules, show the rules for…
- Power Query Not Working in Excel? Causes and FixesIf Power Query is not working in Excel, first open Data > Queries & Connections, right-click the affected…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.