XLOOKUP Excel Cell References: A Practical Guide

In Excel, an XLOOKUP cell reference is the cell or range you tell the function to search or return; for example, =XLOOKUP(E2,A2:A5,B2:B5,"Not found") looks for the value in E2 in A2:A5 and returns the corresponding value from B2:B5. Use dollar signs, such as $A$2:$A$5, when a range must stay fixed as you copy the formula.

What XLOOKUP references mean

XLOOKUP is a lookup function in Microsoft Excel for Microsoft 365, Excel 2021, and later supported versions. Its references are the normal cell addresses and ranges you use elsewhere in formulas. The function connects three things: the value you want to find, the range where Excel should look, and the range containing the answer.

Its arguments, in order, are the lookup value, lookup array, return array, an optional value to show when there is no match, an optional match mode, and an optional search mode. The lookup array and return array should have compatible shapes: for a vertical search, both are usually columns with the same number of rows; for a horizontal search, both are usually rows. Unlike VLOOKUP, the return range does not have to be to the right of the lookup range.

Lookup value: A cell such as E2, a typed value, or a result from another formula.
Lookup array: The cells XLOOKUP searches, such as A2:A100.
Return array: The corresponding cells from which XLOOKUP returns a result, such as C2:C100.
Optional settings: Controls for a not-found result, approximate or wildcard matching, and search direction.

In the usual case, XLOOKUP searches for an exact match by default. That makes the function useful for matching product codes, names, invoice numbers, or other values that should correspond precisely.

A basic cell-reference example

Suppose cells A2:A5 contain employee IDs 1041, 1042, 1043, and 1044, while B2:B5 contain the corresponding names Avery, Morgan, Jordan, and Casey. If E2 contains 1043, this formula returns Jordan:

With employee IDs in A2:A5, names in B2:B5, and the ID to find in E2, enter =XLOOKUP(E2,A2:A5,B2:B5,"Not found"). The result is Jordan, because 1043 is in A4 and the matching name is in B4.

The ranges are paired by position: the first value in A2:A5 corresponds to the first value in B2:B5, the second to the second, and so on. XLOOKUP does not require the data to be sorted for its default exact-match search. If E2 contains an ID that is not in A2:A5, the formula returns the supplied text, Not found, rather than the default not-found error.

A frequent source of mistakes is selecting ranges that start or end on different rows. If the lookup range is A2:A20, the return range should generally contain the corresponding records, such as B2:B20—not B3:B21. Otherwise, Excel may return a value from the wrong record or report incompatible array dimensions.

Relative, absolute, and mixed references

Cell references can change when a formula is copied. A reference such as A2 is relative: copy a formula one row down and Excel normally adjusts it to A3. This is convenient when each row has a different lookup value. However, if the table ranges should remain the same in every copied formula, make those ranges absolute with dollar signs.

With employee IDs in A2:A5, names in B2:B5, and lookup IDs beginning in E2, use =XLOOKUP(E2,$A$2:$A$5,$B$2:$B$5,"Not found"). Here E2 stays relative so it can become E3 or E4 when the formula is filled down, while both source ranges remain fixed. If E2 contains 1043, the result is Jordan.

A dollar sign before the column and row locks both parts: $A$2. A$2 locks only the row, and $A2 locks only the column. Mixed references can be useful in formulas copied across a grid, but ordinary lookup formulas most often use a relative lookup-value reference and fully absolute table ranges.

In Windows desktop Excel, selecting a cell reference while editing a formula and pressing F4 cycles through reference styles. On Mac, Command+T is the usual equivalent in Excel, though keyboard settings can affect function-key behavior. You can also type the dollar signs directly. Locking a reference does not protect the cell from editing; it only controls how the address changes when the formula is copied.

References to other sheets and Excel tables

A lookup range can be on another worksheet. For example, suppose the current sheet has an order code in A2, and a sheet named Products stores codes in column A and product names in column C. The exclamation mark separates a sheet name from its cell reference.

With the order code in A2 on the current sheet, product codes in Products!A2:A50, and product names in Products!C2:C50, use =XLOOKUP(A2,Products!$A$2:$A$50,Products!$C$2:$C$50,"No match"). It returns the product name for the matching code, or No match if that code is absent. If a sheet name contains spaces, surround it with single quotation marks, as in 'Product List'!$A$2:$A$50.

Excel tables offer another way to refer to data. If a table is named Inventory and its headers are SKU and Unit Price, its structured references use those names instead of fixed coordinates. With the SKU to look up in E2 and those columns in the Inventory table, use =XLOOKUP(E2,Inventory[SKU],Inventory[Unit Price],"No match"). When rows are added to a properly maintained table, its column references expand with the table, which can be easier to maintain than manually extending a range.

Returning multiple cells and finding the last match

The return array can contain more than one column. If Excel has room to display the results, XLOOKUP can return a row of related values that spills into neighboring cells. This is useful when a match should return, for example, both a person’s name and department. The cells where results would spill must be empty; otherwise Excel reports a spill error.

With IDs in A2:A5, names in B2:B5, departments in C2:C5, and an ID in G2, enter =XLOOKUP(G2,A2:A5,B2:C5,"Not found"). If G2 contains 1043, the formula returns Jordan in the first result cell and Jordan’s department from C4 in the next cell. The result is not a single combined text value: it is a two-cell spill range.

XLOOKUP can also search from the bottom of a range upward. That is useful when a list contains repeated keys and the desired result is the most recently listed matching row. With IDs in A2:A20, dates in B2:B20, values in C2:C20, and the ID to find in E2, use =XLOOKUP(E2,A2:A20,C2:C20,"Not found",0,-1). If the ID appears more than once, the formula returns the value in column C from its last occurrence in the search range. The 0 specifies exact matching; -1 as the final argument reverses the search direction.

Match modes and sorted data

For ordinary lookups, leaving the match-mode argument out is usually clearest: XLOOKUP uses exact matching. If you need an approximate match, the match-mode argument can request an exact match or the next smaller value, an exact match or the next larger value, or a wildcard match. Wildcards can help with text patterns, but they are not the default behavior.

For example, suppose A2:A5 contains ascending price breakpoints 0, 50, 100, and 200, and B2:B5 contains the associated tier names. With a price of 87 in E2, use =XLOOKUP(E2,A2:A5,B2:B5,"No tier",-1). The result is the tier associated with 50, because that is the exact-or-next-smaller breakpoint. For this kind of threshold lookup, the breakpoint values and the meaning of each tier should be checked carefully; a wrong sort order or unsuitable match mode can produce a plausible but incorrect answer.

Search mode is a separate setting from match mode. It controls the direction of the search, and it can also specify a binary search for data sorted in the required order. Binary search can be faster on large sorted ranges, but it depends on that sorting being correct. For most readers and ordinary-sized lists, the default search is the safer choice.

Common reference problems and fixes

A #N/A result means Excel did not find a match under the formula’s settings, unless the formula supplies a not-found value. Check for extra spaces, different data types, a mistyped lookup value, or a lookup range that omits the relevant row. A number stored as text may not behave like a numeric value, even when both appear visually similar. Consider cleaning or standardizing the source data rather than hiding every mismatch with a substitute result.

A #VALUE! error can indicate incompatible lookup and return array dimensions. Confirm that the ranges are oriented consistently and correspond to the same records. A #SPILL! error commonly means something is blocking the cells needed for a multi-cell result; clear the obstructing cells or move the formula. A #NAME? error can occur when a version of Excel does not recognize XLOOKUP or when a formula contains a misspelled name.

Check that the lookup value is in the type and format you expect.
Confirm the lookup and return ranges align row by row or column by column.
Use absolute references for fixed source ranges when copying a formula.
Check for duplicate keys if the first match is not the record you intended.
For a multi-column result, make sure the destination spill area is clear.

For Excel 2019 and Excel 2016, XLOOKUP is not available as a built-in function. A workbook containing it may therefore show an unrecognized-function error in those versions. Microsoft 365 and Excel 2021 or later include XLOOKUP, with availability also depending on the particular app and update channel. Excel for Mac and Excel for the web support XLOOKUP in current supported versions; the cell-reference principles are the same, although the interface can differ from Windows desktop Excel.

How XLOOKUP compares with older lookup methods

VLOOKUP is familiar and works in older Excel versions, but its lookup column must be the leftmost column of its table array, and its return column is commonly selected with a column number. Inserting or rearranging columns can make that number harder to maintain. XLOOKUP uses separate lookup and return arrays, so it can look left or right and its references say directly which cells to search and return.

INDEX and MATCH are another flexible option, especially in older workbooks or versions without XLOOKUP. They can perform lookups in either direction, but require a combination of functions and careful alignment of their ranges. For an existing workbook, compatibility may matter more than replacing a working formula. If colleagues use Excel versions that do not recognize XLOOKUP, choose a compatible method or confirm that the workbook will be opened in a supported version.

Frequently Asked Questions

Do XLOOKUP ranges need to be the same size?
For a typical one-to-one lookup, the lookup and return arrays should have matching dimensions and correspond to the same records. A vertical lookup range should pair with a vertical return range of the matching height.

Do I have to use dollar signs in XLOOKUP?
No. Use dollar signs when a reference should stay fixed as you copy the formula. A lookup-value reference often remains relative, while the source ranges are made absolute if they should not shift.

Can XLOOKUP return a value to the left?
Yes. The return array is specified separately from the lookup array, so it can be in a column to the left or right of the search column.

Why does my XLOOKUP show #N/A?
The exact lookup value may not be present, or it may differ because of spaces, data type, or spelling. Check the value and the range boundaries; use an optional not-found result only if that is appropriate for the worksheet.

Does XLOOKUP work in Excel 2019?
No. XLOOKUP is available in Microsoft 365 and Excel 2021 or later supported versions, but not as a built-in function in Excel 2019 or Excel 2016. Use a compatible alternative if the workbook must work in those releases.

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

More in Formulas & functions

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

Spotted an error in this guide? Let us know.