Function Problems

How to Fix VLOOKUP Returning #N/A Error in Excel

Bushra Parveen Sep 29, 2026 870 views

Question details

The user needs to understand why their VLOOKUP formula returns an #N/A error for certain rows despite having matching values, and how to resolve the issue.

How to Fix VLOOKUP Returning #N/A Error in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Using the VLOOKUP function to pull data from a reference table into another worksheet or table.
Observed behavior: The formula returns an #N/A error for some cells instead of the expected matched value, even when the formula looks identical and matching data visibly exists.

Before you start

Before troubleshooting your formula, ensure that the value you are looking for actually exists in the first column of your reference table, as VLOOKUP cannot search from right to left.

Solution 1Recommended

Force an Exact Match in the VLOOKUP Formula

Ensure VLOOKUP looks for an exact match to prevent errors caused by unsorted data or accidental approximate matching.

By default, if the fourth argument of VLOOKUP is omitted, Excel assumes an approximate match (TRUE). If your data is not sorted in ascending order, this approximate matching will often result in an #N/A error even when the data is present.

Select the error cell

Click on the cell displaying the #N/A error.

Edit the formula

Check the formula bar to view your current VLOOKUP syntax.

Add the FALSE argument

Add `, FALSE` or `, 0` at the very end of your formula before the closing parenthesis (e.g., change `=VLOOKUP(A2, $H$2:$J$100, 3)` to `=VLOOKUP(A2, $H$2:$J$100, 3, FALSE)`).

Apply to other cells

Press Enter to save the formula, then double-click or drag the fill handle in the bottom-right corner of the cell to apply this corrected formula to the rest of your column.

Force an Exact Match in the VLOOKUP Formula

Locking Reference Cells: Ensure your reference table range is locked using absolute references (e.g., $H$2:$J$100). If the range is not locked, it will shift downwards as you copy the formula, causing #N/A errors.

Solution 2

Remove Hidden Spaces Using the TRIM Function

Fix matching failures caused by invisible trailing or leading spaces in your lookup values.

Solution 3

Fix Text-to-Number Formatting Mismatches

Resolve issues where numbers stored as text prevent VLOOKUP from finding a mathematical match.

Powerful Spreadsheet Tool

Easily Manage Formulas and Fix Errors with WPS Office

WPS Spreadsheet provides a highly compatible and user-friendly environment for managing complex data and executing formulas like VLOOKUP. With built-in error checking and intuitive data cleaning tools, you can easily troubleshoot #N/A errors and format inconsistencies.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your VLOOKUP formulas.
2. Use Error Checking: Navigate to the 'Formulas' tab and click on 'Error Checking' to automatically scan for and trace #N/A errors.
3. Clean mismatched data: Use the 'Text to Columns' tool under the 'Data' tab to quickly convert numbers stored as text into a standard numeric format.
4. Edit function arguments: Click 'Insert Function' (fx) on the formula bar to open a clean dialog box where you can easily ensure your 'Range_lookup' argument is set to FALSE.

Fully compatible with Microsoft Excel formulas, functions, and file formats (.xlsx, .csv).Built-in error checking to quickly identify and fix formula inconsistencies.Smart data cleaning tools to effortlessly remove spaces and convert text formats.Lightweight architecture ensures smooth performance even when querying massive datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why does VLOOKUP work for some rows but return #N/A for others?

This usually happens because the lookup values in the failing rows have slight formatting differences, such as hidden spaces, non-printable characters, or being formatted as text instead of numbers. It can also occur if your table array references are not locked (using $ signs), causing the lookup range to shift downward as you copy the formula down the column.

How do I hide the #N/A error in my VLOOKUP results?

You can wrap your VLOOKUP formula in the IFERROR function to display a custom message or a blank cell instead of the ugly error. For example, use =IFERROR(VLOOKUP(A2, $H$2:$J$100, 3, FALSE), "Not Found") to display 'Not Found', or use "" to leave the cell blank.

Does the VLOOKUP reference table need to be sorted?

If you are using an exact match (having FALSE or 0 as the fourth argument), the reference table does not need to be sorted. However, if you are using an approximate match (TRUE or omitting the fourth argument), the first column of the reference table must be sorted in ascending order; otherwise, VLOOKUP will likely return an incorrect result or an #N/A error.

Excel & Data Problems