XLOOKUP #VALUE! Error: Causes and Fixes

A #VALUE! error in XLOOKUP most often means the lookup range and return range do not have matching dimensions along the direction being searched. Make the two ranges cover the same number of rows for a vertical lookup, or the same number of columns for a horizontal lookup; also check that neither range contains an unintended error or invalid reference.

What XLOOKUP Does

XLOOKUP searches one row or column for a value and returns a corresponding value from another range. Its basic syntax is =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The lookup array is where Excel searches; the return array supplies the result from the matching position. Square brackets in the syntax indicate optional arguments, not characters to type into a formula.

For example, a vertical lookup searches down a column of product codes and returns a price from the same row in a neighboring column. XLOOKUP uses exact matching by default, so you generally do not need to add a match-mode argument for an ordinary exact lookup. It can also return an entire row or multiple columns when the return range is wider than one column, provided its rows line up with the lookup range.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. Older editions that do not support the function typically show #NAME? rather than #VALUE!, so a #VALUE! message usually points to the formula’s arguments, ranges, or underlying data—not simply to an unsupported version.

The Most Common Cause: Mismatched Ranges

For a vertical search, the lookup array must have the same number of rows as the return array. If the lookup range contains nine cells but the return range contains only eight, Excel cannot reliably pair every possible match with a result, and XLOOKUP can return #VALUE!. The same principle applies horizontally: a lookup row and its return range need the same number of columns.

Here is a deliberate mismatch. Assume product codes are in A2:A10 and prices are in B2:B9. If the code to find is in E2, this formula uses nine lookup cells but only eight return cells:

=XLOOKUP(E2,A2:A10,B2:B9)

That mismatch is the first thing to correct. If the price list actually continues through row 10, use the corresponding return range:

With product codes in A2:A10, prices in B2:B10, and the code to find in E2, use:

=XLOOKUP(E2,A2:A10,B2:B10)

The formula returns the price from column B on the row where the code in E2 is found. If the lists genuinely have different lengths, determine which records belong in the lookup rather than extending a range blindly; adding an unrelated row can hide a data-layout problem.

How to Diagnose the Error

Check the formula from the inside out. In Excel for Microsoft 365 on Windows, click the formula cell and inspect the references in the formula bar. You can also use Formulas > Formula Auditing > Evaluate Formula to step through how Excel evaluates the formula. These checks help separate a range-size problem from an error in the source data.

Identify the lookup direction. Is XLOOKUP searching down a column or across a row? A lookup array should be a single row or a single column, not a rectangular block of unrelated values.
Count the relevant cells. For a vertical lookup, compare the number of rows in the lookup and return ranges. For a horizontal lookup, compare their column counts. A return range may have extra columns for a vertical lookup, as long as the corresponding row count matches.
Inspect the range endpoints. A common slip is using A2:A100 for the lookup array and B:B for the return array. Those references have different row counts. Use matching bounded ranges, or use full-column references for both.
Look for broken references and source errors. A deleted row or column can create a #REF! reference, while a value used in one of the ranges may itself display an error. Correct the source problem before adding error-handling functions.
Check all six arguments. Confirm commas separate the arguments, parentheses are balanced, and optional match_mode or search_mode values are intentional. A typo or an argument in the wrong position can change what the formula means.

If the formula is long, temporarily select each referenced range in the formula bar or replace it with a short, clearly bounded range to isolate the issue. Do not assume that a displayed #VALUE! always means “no matching record”; a missing match ordinarily produces #N/A unless you supply an if_not_found result.

Correct Vertical and Horizontal Lookups

In a small vertical example, suppose A2:A6 contains the codes P-101, P-102, P-103, P-104, and P-105, while B2:B6 contains their prices: 8.5, 12.5, 6, 19, and 4. E2 contains P-102. Both ranges cover five rows, so the lookup and return positions correspond.

With product codes in A2:A6, prices in B2:B6, and the requested code in E2, this formula returns 12.5:

=XLOOKUP(E2,A2:A6,B2:B6,"Not found")

For a horizontal lookup, assume the month labels are in B1:E1 and the sales figures are directly beneath them in B2:E2. If G1 contains Mar, the matching month and sales ranges each span four columns.

With month labels in B1:E1, sales in B2:E2, and the month to find in G1, this formula returns the value in D2:

=XLOOKUP(G1,B1:E1,B2:E2,"Not found")

Returning multiple fields is also possible. If codes are in A2:A6 and product names and prices are in B2:C6, the lookup array has five rows and the return array has those same five rows across two columns. XLOOKUP can return the matching name and price together; the number of columns in the return range does not need to equal the width of the one-column lookup array.

References That Commonly Get Out of Sync

Range mismatches often arise after data is expanded, copied, or converted into a different layout. For example, someone may add a new record to the lookup column but forget to extend the return range. Copying a formula down can also shift one reference while another remains fixed, especially when absolute-reference dollar signs are applied inconsistently.

Whole-column references are one way to keep two ordinary column references aligned. With product codes anywhere in column A, prices in column B, and the requested code in E2, this formula searches both complete columns:

=XLOOKUP(E2,A:A,B:B,"Not found")

That approach can be convenient, but bounded ranges or Excel Tables can make a workbook easier to understand and maintain. A Table’s structured references expand as records are added, and using corresponding columns from the same table helps keep the lists aligned. Still verify that the lookup and return references point to the intended columns; a formula can have compatible dimensions and return the wrong field.

Spilled arrays and formulas that create dynamic ranges can also make the actual size less obvious. If one range changes shape based on its contents, check the resulting spill area and ensure the lookup and return arrays still have matching lengths along the search direction. A blocked spill normally has its own #SPILL! error, so distinguish that message from an XLOOKUP #VALUE! result.

Distinguishing #VALUE! from Other XLOOKUP Errors

Knowing the error type prevents an unhelpful fix. XLOOKUP errors do not all mean the same thing:

#VALUE! Often signals incompatible range dimensions or a problem in an argument or evaluated source. Start by checking the lookup and return ranges.
#N/A Usually means an exact match was not found. Check spelling, extra spaces, number-versus-text differences, and whether the value exists. An optional if_not_found argument can display a friendlier result when no match is found.
#REF! Means a reference is invalid, often because a referenced row, column, or range was deleted or changed.
#NAME? Can indicate that Excel does not recognize the function name, including when a workbook is opened in an edition that does not support XLOOKUP.
#SPILL! Means a dynamic-array result cannot occupy the required cells, commonly because something is blocking the output area. It is not the same as a mismatch between lookup and return ranges.

A related misconception is that XLOOKUP returns #VALUE! whenever it cannot find a match. In a normal exact lookup, an absent match returns #N/A by default. You can provide an if_not_found value such as Not found, but that option does not repair invalid range sizes or other formula errors.

When Error-Handling Functions Help—and When They Hide the Cause

IFNA can replace a genuine not-found result with a clear message. IFERROR can replace a wider set of formula errors, including #VALUE!. Neither function corrects the underlying formula. If a mismatched range is wrapped in IFERROR, the workbook may show a reassuring label instead of revealing that the lookup is structurally wrong.

Use an if_not_found argument when the intended case is simply that no record exists. Reserve broader error handling for situations where you have decided what each possible error should mean and have a way to investigate unexpected failures. During troubleshooting, temporarily remove IFERROR or similar wrappers so the original error is visible.

If a lookup range contains an error value from another formula, inspect that source cell rather than assuming XLOOKUP is responsible. Repairing the source formula can resolve the problem at its origin and prevent other calculations from inheriting the same error.

Preventing the Error in Future Formulas

A consistent data structure reduces the chance of range drift. Keep each record on one row, avoid inserting unrelated totals inside a lookup list, and use matching endpoints when referencing ordinary ranges. When a list grows frequently, an Excel Table can make formulas more maintainable than manually adjusting fixed row numbers.

Before relying on a lookup, confirm three things: the lookup array runs in the intended direction, the return range aligns with it, and the lookup value uses a compatible representation. For example, the text value 00125 and the number 125 are not necessarily interchangeable in a lookup. That kind of mismatch is more likely to cause a not-found result than a range-dimension #VALUE!, but checking it is useful when correcting the first problem reveals a second one.

Finally, test a known value near the beginning, middle, and end of the list. Those checks can reveal an omitted first or last row that a single successful lookup might not expose. If the result should be exact, leave the default match mode in place unless you have a specific reason to request a different match behavior.

Frequently Asked Questions

Does XLOOKUP return #VALUE! when it cannot find a match?
No. A standard XLOOKUP exact search returns #N/A when there is no match. Supply an if_not_found argument if you want a custom message for that case; it will not fix a mismatched range.

Can the XLOOKUP return range have more columns than the lookup range?
Yes. A one-column lookup range can correspond to a multi-column return range, as long as the ranges have the same number of rows. The formula can then return multiple values from the matching row.

Why does XLOOKUP work in one row but fail after I copy it?
Copying may shift a relative reference so that the lookup and return ranges no longer cover corresponding cells. Inspect the formula in the failing row and use absolute references or a Table reference where appropriate to keep the intended ranges aligned.

Will IFERROR fix an XLOOKUP #VALUE! error?
It can hide the displayed error by returning a replacement value, but it does not repair the formula or its source data. Check the ranges and arguments first; use error handling only when you deliberately want to manage that error.

Why do I see #NAME? instead of #VALUE! for XLOOKUP?
#NAME? can mean the Excel edition does not recognize XLOOKUP or that the function name was entered incorrectly. XLOOKUP is supported in Microsoft 365 and Excel 2021 or later; older editions may require another lookup method.

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.