Excel XLOOKUP Value Error? How to Find and Fix It Fast

An Excel XLOOKUP #VALUE! error most often means the lookup array and return array do not have matching dimensions, or the formula contains an invalid search mode. Check those arguments first, then confirm that the ranges point to the intended data and that any errors in the input are not being passed through.

What the XLOOKUP #VALUE! error means

XLOOKUP searches one array for a value and returns a corresponding value from another array. In a typical formula, the lookup array and return array have the same number of rows or columns. If they do not line up, Excel cannot pair each possible match with a corresponding result, so it returns #VALUE!.

For example, a lookup range of five cells paired with a return range of four cells is an incompatible setup. The problem is not necessarily the value you are searching for. The formula can return #VALUE! even when the lookup value exists, because Excel cannot perform the requested search-and-return operation with arrays of different sizes.

An invalid search_mode argument is another direct cause. XLOOKUP accepts 1 or -1 for a linear search, and 2 or -2 for binary search. Supplying another number, such as 3, can produce #VALUE!. Errors contained in referenced inputs can also affect the formula, so inspect the source cells if the ranges appear correct.

How XLOOKUP uses its arrays

The function has this general structure: =XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]). The first argument is what to find. The second is where to look. The third is where to retrieve the corresponding result. The remaining arguments are optional: one can supply a custom result when there is no match, one controls the kind of match, and one controls search direction or method.

Lookup and return arrays must correspond in shape. A vertical lookup array can return values from a vertical range with the same number of rows; a horizontal lookup array can return values from a horizontal range with the same number of columns. The two ranges do not need to sit beside each other, but their positions must align: the first lookup item maps to the first return item, the second to the second, and so on.

Assume product codes are in A2:A6, prices are in B2:B6, and E2 contains P-104. If the intended price is $29, this formula has a mismatch because the lookup range contains five cells and the return range contains four:

With product codes in A2:A6, prices in B2:B6, and P-104 in E2, this deliberately incorrect formula returns #VALUE!:

=XLOOKUP(E2,A2:A6,B2:B5)

With the same layout, this corrected formula returns 29:

=XLOOKUP(E2,A2:A6,B2:B6)

Check both the length and orientation of the ranges. For instance, pairing A2:A6 with B2:F2 is not a valid way to match five vertical records to five horizontal cells. Transposing one range may make the dimensions look numerically similar, but XLOOKUP needs arrays that correspond in the intended orientation.

Check the formula arguments in order

When the formula is long, inspect it from left to right rather than changing several parts at once. The comma-separated arguments can be easy to misread, especially when optional arguments have been omitted.

Confirm the lookup value. Check that the cell reference points to the value you mean to find, not a nearby heading or blank cell.
Inspect the lookup array. Make sure it contains the search values and is a single row or column for the usual lookup setup.
Inspect the return array. Count its rows or columns and compare them with the lookup array. Confirm that its first item belongs with the first lookup item.
Review optional arguments. An argument entered in the wrong position can be interpreted as a different option than intended. XLOOKUP uses match_mode before search_mode.
Look for errors in referenced cells. Trace the source values and check whether a cell used as an input already displays an Excel error.

For exact matching, match_mode 0 is the default, so it can usually be left out. The other documented match modes are -1 for exact match or next smaller item, 1 for exact match or next larger item, and 2 for wildcard matching. Search_mode 1 searches first to last by default; -1 searches last to first; 2 and -2 request binary searches in ascending and descending order respectively.

Fix an invalid search mode

A search mode is the sixth argument, after the optional not-found and match-mode arguments. Because those earlier arguments can be omitted, a formula with several commas may be difficult to read. This example intentionally supplies an unsupported search mode:

With product codes in A2:A6, prices in B2:B6, and P-104 in E2, this formula uses 3 as the search mode and returns #VALUE!:

=XLOOKUP(E2,A2:A6,B2:B6,,0,3)

For an ordinary exact lookup, remove the unnecessary options or use the default search. With the same layout, the shorter formula below returns 29:

=XLOOKUP(E2,A2:A6,B2:B6)

Binary search modes require the lookup array to be sorted in the appropriate order. With mode 2, values should be in ascending order; with mode -2, they should be in descending order. An incorrectly sorted range can make binary search return an incorrect result; do not rely on it to raise a helpful error. If the data is not reliably sorted, use the default linear search instead.

Distinguish #VALUE! from other lookup errors

Not every failed lookup is a #VALUE! problem. Identifying the actual error narrows down what to check:

#N/A usually means XLOOKUP did not find a match. Check the spelling, spaces, data type, and exact-match setting. The optional if_not_found argument can provide a message when no match exists.
#REF! indicates an invalid reference, such as a range reference that no longer points to a valid cell.
#NAME? can mean Excel does not recognize the function name, including when the workbook is opened in an Excel version that does not support XLOOKUP.
#SPILL! can occur when a formula needs to return multiple results into neighboring cells but something blocks the spill range.
#VALUE! points attention to incompatible arguments or dimensions, an invalid search mode, or an error passed in from an input.

The if_not_found argument is not a universal error handler. It is designed for the case where a match is not found; it does not repair mismatched arrays or an invalid search mode. Replacing the displayed error with a custom message can hide the symptom without correcting the formula.

Check data types and source values

If the ranges have matching dimensions and the search mode is valid, check the cells feeding the formula. A lookup value or source cell may itself contain an error produced by another formula. Follow the references back until you find the first cell with an error, then correct that upstream issue.

Also check whether values that look identical are stored differently. For example, a product code may include a leading or trailing space, or a numeric identifier may be stored as text in one range and as a number in another. Such differences more commonly cause #N/A than #VALUE!, but they are worth checking when the formula structure is sound. Do not convert identifiers to numbers automatically if leading zeros are meaningful: 0042 and 42 may represent different codes.

Useful checks include comparing the actual cell contents in the formula bar, looking for accidental spaces, and verifying that the intended lookup and return ranges start on corresponding data rows. If the worksheet contains inserted rows or copied formulas, inspect whether one reference shifted while the other remained fixed. Absolute references such as $A$2:$A$6 can prevent ranges moving when a formula is copied, but they do not fix a range that was defined incorrectly in the first place.

A practical troubleshooting sequence

Use a small, controlled check before rebuilding a complex formula. Select the formula cell and examine the formula bar. In Windows Excel, F2 lets you edit the active cell; on a Mac, use Control+U to edit the active cell. You can also select individual references in the formula bar to see which cells they cover.

Count the cells in the lookup and return arrays. Confirm equal lengths and matching orientation.
Temporarily simplify the formula to the lookup value, lookup array, and return array. Remove optional arguments unless they are needed.
Test with a lookup value you know is present and note the expected return value.
If the simplified formula works, add optional arguments back one at a time, checking that match_mode and search_mode occupy the correct positions.
If it still errors, inspect the source cells and verify that the function is supported by the version of Excel being used.

This approach separates a structural problem from a data problem. For example, if a formula works with the three basic arguments but fails after adding a sixth argument, the search-mode value deserves attention. If it fails even in the simplified form, start with the ranges and their source cells.

Version and compatibility notes

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including current Excel for Windows, Mac, and the web. It is not available as a native worksheet function in Excel 2019 or Excel 2016. In an older version, a workbook containing XLOOKUP may show an unsupported-function marker such as _xlfn.XLOOKUP or produce an unrecognized-function error.

If someone who uses an older Excel version must work with the file, consider replacing the formula with a compatible alternative or ask that person to open it in a supported version. VLOOKUP or INDEX with MATCH can cover many basic lookup tasks, but their argument rules and capabilities differ. XLOOKUP can return values to the left or right of the lookup array, while VLOOKUP generally requires the lookup column to be the leftmost column in its table range. Changing functions does not automatically correct bad source data or mismatched references.

Formula argument separators may also vary with regional settings. This article uses commas, as in standard English-language Excel examples. If Excel on your computer expects semicolons, use the separator shown by your installation; that setting difference is separate from the meaning of the arguments.

Frequently Asked Questions

Why does XLOOKUP return #VALUE! when the value exists?
The match may be present, but XLOOKUP can still return #VALUE! if the lookup and return arrays do not have matching dimensions or if the search_mode is invalid. Compare the ranges before changing the lookup value.

Can mismatched range sizes cause an XLOOKUP error?
Yes. The lookup array and return array need matching row or column counts and must correspond item by item. A five-cell lookup range paired with a four-cell return range can produce #VALUE!.

Does XLOOKUP need the lookup column sorted?
No, not for its default search. The default linear search works without sorted data. Sorting is required for the binary search modes 2 and -2; use the default search when the data order is not guaranteed.

Will if_not_found fix an XLOOKUP #VALUE! error?
Usually not. That optional argument supplies a result when the lookup finds no match, which is normally an #N/A situation. It does not correct incompatible array sizes or an invalid search mode.

Why does XLOOKUP work on one computer but not another?
The computers may use different Excel versions. XLOOKUP is supported in Microsoft 365 and Excel 2021 or later, but not Excel 2019 or Excel 2016. An older installation may not recognize the function.

What should I check first if I am unsure of the cause?
Compare the lookup and return ranges for equal dimensions, then check that the sixth argument is a valid search mode or omitted. If those are correct, simplify the formula and inspect errors or inconsistent values in the referenced cells.

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.