Excel VLOOKUP Value Not Available Error?

In Excel, the VLOOKUP “value not available” error is usually #N/A: Excel cannot find the lookup value in the first column of the selected range. For an exact lookup, set VLOOKUP’s last argument to FALSE, then check that the lookup value and source data match exactly, including their data types and spaces.

What the VLOOKUP #N/A error means

VLOOKUP searches down the leftmost column of a range for a value, then returns a value from another column in the same row. When an exact-match lookup does not find the requested value in that first column, Excel returns #N/A, meaning the value is not available in the lookup range. It does not necessarily mean the value is absent from the entire worksheet: it may be outside the selected range or in a column VLOOKUP is not searching.

The error is different from #REF!, which can indicate an invalid column number in the formula, and #VALUE!, which points to an argument or value problem. Those errors need different checks. If your result is specifically #N/A, start by checking the lookup column and the exact contents of both values.

A common misconception is that VLOOKUP automatically searches every column in a table. It does not. It searches only the first column of the range supplied in the formula, and it can return values only from columns to the right of that first column.

Start with an exact-match formula

For product codes in B2:B5, prices in C2:C5, and the code to find in E2, use this formula. The lookup range begins with the codes, and the final argument forces an exact match:

=VLOOKUP(E2,$B$2:$C$5,2,FALSE)

If E2 contains SKU-103 and that code is in B4, the formula returns the price in C4. The number 2 tells Excel to return the second column within B2:C5; FALSE tells it not to accept an approximate match. Dollar signs keep the lookup range fixed if you copy the formula to other cells.

To enter the formula, select the result cell, type it, and press Enter. In Microsoft 365 for Windows, the same formula syntax works in Excel for the web and on Mac. If you still see #N/A, the next sections explain how to locate the mismatch rather than hiding it.

How VLOOKUP searches and chooses a result

VLOOKUP’s syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The lookup value is what Excel searches for; the table array is the complete range; the column index is the return column’s position inside that range; and the optional last argument selects exact or approximate matching.

When the last argument is FALSE or 0, VLOOKUP looks for an exact match. If you omit that argument, Excel uses approximate matching. Approximate matching is useful for categories such as tax bands or grading thresholds, but it is not the safe default for product IDs, names, or account numbers. For approximate matches, the first column should be sorted in ascending order; otherwise results may be incorrect.

For example, with ascending minimum-score thresholds 0, 50, and 100 in B2:B4, corresponding labels in C2:C4, and a score of 72 in E2, this approximate-match formula returns Silver if the label beside 50 is Silver:

=VLOOKUP(E2,$B$2:$C$4,2,TRUE)

It returns the label associated with the largest threshold that is less than or equal to 72. For a unique code that must match exactly, use FALSE instead.

Common causes of #N/A

The value is not in the first column of the selected range. If the code is in column C but your table array starts at D, VLOOKUP cannot find it. Change the range so its leftmost column contains the lookup values.
The lookup range stops too early. A formula using B2:C20 will not find a key in B21. Extend the range or use a table-based reference that grows when records are added.
One value is text and the other is numeric. A cell containing the number 123 may not match a cell storing the text “123”. This frequently happens with imported data. Check the cells’ contents and number formats; changing a display format alone does not always convert the underlying value.
There are leading, trailing, or hidden spaces. “North” and “North ” look alike but are not the same text for an exact lookup. Imported web data may also contain a nonbreaking space, which ordinary trimming may not remove.
The values differ by punctuation or characters. A hyphen, apostrophe, accent, or extra zero can make two identifiers different. For example, 00427 and 427 may represent the same ID to a person but not to Excel, especially if one is stored as text.
The formula points to the wrong column or sheet. A copied formula may have shifted its range, or the data may be on a different worksheet. Confirm the selected range directly in the formula bar.
The lookup value itself is an error. If the cell being searched already contains an error such as #N/A, the lookup cannot produce the expected ordinary result. Trace that cell’s formula first.

Excel’s exact text matching is generally not case-sensitive, so “abc” and “ABC” normally match. Case differences are therefore less likely to explain a VLOOKUP #N/A than spaces, data types, or a range that misses the record.

A practical way to diagnose the mismatch

Click the formula cell and confirm that the last argument is FALSE for a code or name lookup.
Check the first column of the table array. Search that column, not the whole worksheet, for the expected key.
Compare the lookup cell with the source cell in the formula bar. Look for spaces, punctuation, extra zeros, or differences in how the values are stored.
Temporarily select the lookup range in the formula bar and make sure it includes the row containing the match. Check that the return column is inside the range too.
Test one known key that should match. If it works, the formula structure is probably sound and the failing record likely contains a data mismatch or is missing.

For imported text, a helper column can normalize ordinary leading and trailing spaces. With the unclean text in B2, enter this in a spare cell and fill it down; it returns the text with leading and trailing spaces removed:

=TRIM(B2)

This only helps if the lookup value and lookup-column values are cleaned consistently. TRIM does not remove every hidden character, including some nonbreaking spaces, and it cannot make a text number the same underlying value as a numeric one. For those cases, inspect a sample and choose a consistent cleanup or conversion method before changing a whole dataset.

Show a useful message without hiding the cause

If a missing item is an expected possibility, you can replace only #N/A with a clear message. With the same product-code layout—codes in B2:B5, prices in C2:C5, and the requested code in E2—use:

=IFNA(VLOOKUP(E2,$B$2:$C$5,2,FALSE),"Not found")

If E2 contains a code absent from B2:B5, the result is Not found. If a match exists, the formula returns its price. IFNA is often preferable to IFERROR for this purpose because it catches the specific “not available” error rather than also concealing other problems, such as an invalid column reference.

Use a message that fits the task. For an order report, “Product code not found” may be more informative than a blank. Avoid replacing errors with an empty string during troubleshooting: a blank can make missing records difficult to notice. Fix the lookup first, then decide how the finished report should display genuinely absent items.

When to use XLOOKUP or another approach

In Microsoft 365, XLOOKUP is a useful alternative. It separates the lookup column from the return column, defaults to exact matching, and can supply a not-found message. With codes in B2:B5, prices in C2:C5, and the requested code in E2, this returns the matching price or the text Not found:

=XLOOKUP(E2,$B$2:$B$5,$C$2:$C$5,"Not found")

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It can return values from columns on either side of the lookup column, unlike VLOOKUP. If a workbook must be opened in an older Excel version, check compatibility before replacing established formulas; older versions may not recognize XLOOKUP.

VLOOKUP remains practical when the lookup column is at the left of a compact table and the workbook needs broad compatibility. If you keep it, use an explicit exact-match argument for identifiers and make the range easy to verify. For large or frequently updated datasets, an Excel Table can help keep references aligned as rows are added, although the key still has to be in VLOOKUP’s first table column.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is visibly there?
It may be outside the formula’s selected range, stored as a different data type, or have a hidden space or character. Compare the lookup value with the cell in the first column of the actual table array, not just a similar-looking value elsewhere.

Does VLOOKUP require an exact match?
No. Use FALSE as the fourth argument to require an exact match. If you omit the argument, Excel uses approximate matching, which is intended for sorted thresholds rather than ordinary IDs and names.

Can VLOOKUP search to the left?
No. VLOOKUP searches the first column of its table array and returns a value from a column to its right. Put the lookup field first in the selected range or use a function such as XLOOKUP in supported Excel versions.

Will IFERROR fix the VLOOKUP problem?
It can replace an error with a message, but it does not make the missing value match. Prefer IFNA when you only want to handle #N/A, so other formula errors remain visible and can be corrected.

Why does VLOOKUP work for some rows but not others?
The formula may be correct while particular records contain extra spaces, inconsistent text-versus-number storage, different punctuation, or keys missing from the source list. Check a failing record against a successful one and verify that both are within the same lookup range.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.