XLOOKUP and VLOOKUP Not Working? Fix Common Errors

If XLOOKUP or VLOOKUP is not working, first check that the lookup value and lookup column contain the same kind of data, then confirm the formula points to the intended ranges and uses exact-match settings. For Excel for Microsoft 365 on Windows, XLOOKUP is usually the simpler fix; VLOOKUP remains useful, but its column-index and approximate-match defaults can cause mistakes.

Start with the error or unexpected result

The symptom gives you a useful clue. A formula returning #N/A usually means Excel did not find a match under the formula’s current rules. A wrong result often indicates approximate matching, a miscounted VLOOKUP column index, or a lookup range that includes the wrong data. #NAME? can mean Excel does not recognize the function name. #REF! points to a broken reference, while #VALUE! can arise from an incompatible argument or range.

Before rebuilding the formula, inspect the cell itself. Select it and look at the formula bar to see whether the formula is intact and whether a cell reference has shifted. If you see the formula text in the cell instead of its result, the cell may be formatted as Text, or the formula may have a leading apostrophe. Change the format to General, then re-enter the formula; changing the format alone does not always convert an existing text entry into a working formula.

If you see #N/A, test whether the lookup value exists exactly in the lookup column.
If you see a plausible but incorrect result, set an explicit exact-match option and verify the ranges.
If you see #NAME?, check spelling and whether your Excel version supports the function.
If you see formula text, check the cell format and any leading apostrophe.

Know how each lookup function behaves

XLOOKUP searches one range and returns a value from a separate result range. The lookup and result ranges can be in different columns, and the result column can sit to either side of the lookup column. In current Excel, XLOOKUP uses exact matching by default. Specifying match mode 0 makes that intention visible and avoids relying on an implicit setting.

VLOOKUP searches in the first column of a table array and returns a value from a column to its right. Its third argument is a number counting columns from the left edge of that array—not a worksheet column letter. Its fourth argument controls matching: use FALSE for an exact match. If that argument is omitted, VLOOKUP assumes an approximate match, which can return a misleading result when the first column is not sorted as required.

With product IDs in A2:A100, prices in C2:C100, and the ID to find in E2, this XLOOKUP returns the matching price or the text Not found:

=XLOOKUP(E2,A2:A100,C2:C100,"Not found",0)

With the same data arranged in A2:C100, this VLOOKUP returns the third column’s value and requires an exact match:

=VLOOKUP(E2,A2:C100,3,FALSE)

If E2 contains ID P-204 and its row has 18.5 in column C, each formula returns 18.5. The key difference is structural: XLOOKUP names the result range directly, while VLOOKUP counts to the result column. That count is a common source of errors after columns are inserted or the selected table range changes.

Fix #N/A and data that looks identical

Two values can look the same on screen but differ internally. A code stored as text will not necessarily match the same-looking value stored as a number. For example, the text value “0072” and numeric value 72 are different; converting the code to a number also loses its leading zero. Decide whether the field is an identifier or a quantity before changing its type. IDs, postal codes, and account numbers are often best kept as text.

Other invisible differences include leading or trailing spaces, nonbreaking spaces copied from a website, and nonprinting characters. Formatting differences, such as different font or number formats, generally do not prevent a match; the underlying value is what matters. Case is usually not the problem because ordinary XLOOKUP and VLOOKUP matching is not case-sensitive.

With a messy lookup value in A2, this helper formula removes ordinary excess spaces, nonprinting characters, and nonbreaking spaces:

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

It can help create a cleaned helper column, but cleaning only the lookup value will not fix dirty entries in the source list. Apply consistent cleanup to both sides, preferably in helper columns, then point the lookup formula at the cleaned values. TRIM does not remove every possible Unicode whitespace character, so unusual imported data may need additional investigation.

Check the ranges, column number, and references

Make sure the lookup range contains the values you intend to search and the result range lines up row by row. XLOOKUP’s lookup and return arrays must have compatible dimensions: for a vertical search, use vertical ranges with the same number of rows; for a horizontal search, use matching horizontal ranges. If one range starts on row 2 and the other on row 3, the formula can return the wrong row’s result even though it finds a match.

For VLOOKUP, the first column of the table array must contain the lookup values. If the lookup key is in column C and the result is in A, VLOOKUP cannot search left within A:C; reorder the source or use XLOOKUP. Also recount the return-column index from the left edge of the selected table array. If the array is B:D, column D is index 3, not 4.

References that move when a formula is copied can also break lookups. If a formula in F2 should always search A2:A100 and return C2:C100 as it is filled down, lock those ranges with dollar signs:

=XLOOKUP(E2,$A$2:$A$100,$C$2:$C$100,"Not found",0)

Here E2 remains relative so it changes to E3 in the next row, while the source ranges stay fixed. Alternatively, Excel Tables can make formulas easier to maintain as rows are added, because structured references expand with the table. Confirm that the formula refers to the intended table columns rather than an old fixed range.

Choose exact or approximate matching deliberately

Most searches for an employee ID, product code, or customer name need an exact match. XLOOKUP’s default is exact, but entering 0 as the match-mode argument documents the choice. In VLOOKUP, enter FALSE as the fourth argument. Do not omit that argument merely because the results appear right in a small test; approximate matching may behave differently as the data changes.

Approximate matching has legitimate uses, such as assigning a rate based on a threshold. In that situation, VLOOKUP with TRUE or an omitted final argument expects the first column to be sorted in ascending order. XLOOKUP offers explicit match modes for exact or next-smaller/next-larger matching, and its search mode can also change search direction. These options are separate: match mode determines what counts as a match, while search mode determines how the search proceeds. Use them only when the data rules require them, and verify boundary cases such as values equal to a threshold.

Check Excel version and calculation settings

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but it is not available in Excel 2019 or Excel 2016 as a built-in function. In an older installation, XLOOKUP may produce #NAME? or fail to calculate. Check File > Account in Windows Excel for product and update information, or use VLOOKUP or another compatible approach if the workbook must work in an older version. Recipients using unsupported Excel versions may also be unable to calculate a workbook that contains XLOOKUP.

If formulas appear not to update after source data changes, check Formulas > Calculation Options and select Automatic if appropriate. In Manual mode, Excel waits for recalculation, so displayed values can be stale. The Excel desktop app for Mac and Excel for the web also support XLOOKUP in current supported versions; ribbon appearance can differ, but the function arguments and core troubleshooting principles are the same. Availability depends on the Excel version, not simply on whether the computer is a PC or a Mac.

A practical repair sequence

Use this order to isolate the cause instead of changing several parts of a formula at once:

Read the error and inspect the formula bar. Confirm the function name, parentheses, commas, and cell references.
Find the lookup value directly in the source lookup column. Check for spaces, different data types, and missing leading zeros.
Set exact matching explicitly: use 0 in XLOOKUP’s match-mode position or FALSE as VLOOKUP’s fourth argument.
Verify the search and result ranges start and end on corresponding rows. For VLOOKUP, confirm the key is in the table’s first column and recount the return index.
Test a known record whose correct result you can verify. If it works, compare that record’s underlying data with the failed one.
Check function support and calculation mode, particularly when the file is opened on another computer or in an older Excel version.

When wrapping a lookup in IFERROR, do so only after the lookup works as intended. IFERROR can hide a broken reference or an unexpected formula error by replacing it with a friendly message, which makes diagnosis harder. XLOOKUP already has an optional not-found argument for handling a genuine missing match; it does not suppress unrelated errors caused by a malformed formula or invalid range.

Frequently Asked Questions

Why does XLOOKUP return #N/A when the value is there?
Check for text-versus-number differences, extra spaces, nonbreaking spaces, or an incorrect lookup range. The displayed value may look identical while its stored value differs. Verify that the intended match mode is exact and that the value is in the range the formula actually searches.

Why does VLOOKUP return the wrong row?
The formula may be using approximate matching because its fourth argument is missing or TRUE. Add FALSE for exact matching. If approximate matching is intentional, sort the first table column in ascending order and confirm the threshold behavior.

Can VLOOKUP search to the left?
No. VLOOKUP searches the first column of its table array and returns a result from a column to the right. Use XLOOKUP, which can return a value from either side of the lookup column, or rearrange the data for a VLOOKUP-compatible layout.

Why does XLOOKUP show #NAME?
Check that the function is spelled correctly and that the Excel version supports it. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not as a built-in function in Excel 2019 or Excel 2016.

Does XLOOKUP match uppercase and lowercase differently?
No. Standard XLOOKUP matching is not case-sensitive, and VLOOKUP is also not case-sensitive. If capitalization is the only difference, it ordinarily will not explain a missing match; check other hidden differences instead.

Should I replace every VLOOKUP with XLOOKUP?
Not necessarily. XLOOKUP is more flexible about result-column position and makes the return range explicit, but VLOOKUP may be suitable for a stable, backward-compatible workbook. Consider the Excel versions used by everyone who needs to open and recalculate the file.

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.