Excel XLOOKUP Function Examples for Everyday Lookups

In Excel for Microsoft 365, XLOOKUP finds a value in one range and returns the corresponding value from another. With IDs in A2:A6, names in B2:B6, and the ID 113 in E2, use =XLOOKUP(E2,A2:A6,B2:B6,"Not found"); if ID 113 belongs to Mira Chen, the result is Mira Chen.

What XLOOKUP Does

XLOOKUP is a lookup function for matching a value in a list and retrieving related information. Its main advantage is that the lookup range and return range are separate arguments: you can search one column and return a value from a column to its left or right. You can also search a row, return more than one column, specify what appears when there is no match, and control the type and direction of the search.

The function is available in Microsoft 365 and Excel 2021 or later. It is not included in older perpetual versions such as Excel 2019. If someone opens a workbook containing XLOOKUP in an unsupported Excel version, the function may appear as an unrecognized name rather than calculate normally.

The general syntax is XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]). The first three arguments are essential. The remaining arguments are optional and let you define a fallback result, approximate or wildcard matching, and the search direction.

Basic Exact-Match Example

Suppose a small inventory list has product IDs 104, 108, 113, 121, and 126 in A2:A6, with product names in B2:B6: Notebook, Pen set, Desk lamp, Mouse, and USB hub. Cell E2 contains 113. With those values in place, the formula is:

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

The result is Desk lamp. XLOOKUP uses exact matching by default, so there is no need to add a match-mode argument for an ordinary ID or name lookup. The optional fourth argument supplies a clear message if the searched-for ID is absent; without it, a missing exact match returns the error #N/A.

The ranges need to line up: A2:A6 and B2:B6 both contain five rows, and each name is on the same row as its product ID. A return range of a different height can produce an error. When building a lookup, check that each lookup value and its associated result occupy corresponding positions.

Choose a Useful Not-Found Result

A fallback message can make a report easier to read than a visible #N/A error. It can also help distinguish an expected missing record from other formula problems. For example, if the inventory list above has product IDs in A2:A6, names in B2:B6, and the requested ID in E2, use:

=XLOOKUP(E2,A2:A6,B2:B6,"No matching product")

If E2 contains 113, the result is Desk lamp; if it contains 999 and that ID is not listed, the result is No matching product. The fallback text is an optional XLOOKUP argument, not a general replacement for error handling: it only handles the lookup’s not-found case. If a formula returns an unexpected error for another reason, this message will not necessarily conceal it.

Choose wording that makes sense in the worksheet. “Not listed” may suit a data-entry form, while a blank result may be appropriate in a presentation sheet. To return a blank, put an empty text value in the fourth argument. Be cautious with blanks in analysis: they can make an absent match look like a genuine empty value.

Lookups to the Left and in Rows

Unlike VLOOKUP, XLOOKUP does not require the return range to be to the right of the lookup range. With product names in B2:B6, product IDs in A2:A6, and the requested name in E2, this formula searches names and returns IDs:

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

If E2 contains Mouse, the result is 121. This freedom is useful when a workbook’s existing column order is inconvenient. It also means you do not need to rearrange source data merely to make a lookup possible.

XLOOKUP can search horizontally as well. Suppose quarter labels are in B1:F1 and corresponding revenue amounts are in B2:F2, with Q3 entered in H1. Use:

=XLOOKUP(H1,B1:F1,B2:F2,"Quarter not found")

If the values are Q1, Q2, Q3, Q4, and Q5, and the corresponding amounts are 120000, 145000, 180000, 172000, and 201000, the result for Q3 is 180000. The key is to provide a row for both the lookup array and return array, with matching positions.

Return the Last or Most Recent Match

By default, XLOOKUP searches from the first item toward the last and returns the first match it encounters. If an ID appears more than once and the most recent entry is at the bottom of a list, set the sixth argument, search_mode, to -1. For IDs in A2:A6, statuses in B2:B6, and the requested ID in E2, use:

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

For example, if ID 113 appears in A3 and A6, with status values Pending in B3 and Complete in B6, and E2 is 113, the formula returns Complete. The 0 explicitly requests exact matching; -1 tells XLOOKUP to search from the last item backward. This returns the last matching row in the range, which is only the most recent record if the data is arranged chronologically or otherwise ordered that way.

Reverse searching can also be useful when a log is appended over time. It does not sort the data or verify dates, however. If records can be entered out of order, sort or check the date column separately before treating the last row as the newest record.

Wildcard and Approximate-Match Examples

For text searches where the user may know only part of a value, set match_mode to 2 to enable wildcards. An asterisk matches any sequence of characters, and a question mark matches one character. With product names in B2:B6, IDs in A2:A6, and *lamp* entered in E2, this formula returns the ID for a name containing “lamp”:

=XLOOKUP(E2,B2:B6,A2:A6,"No match",2)

If the list contains Desk lamp with ID 113, the result is 113. Wildcard matching is useful for partial text, but it is not a substitute for a carefully defined identifier. If several entries contain the same text, XLOOKUP returns the first match by default. A tilde can be used to treat a wildcard character as a literal character in a search pattern.

Approximate matching is useful for lookup tables that define bands, such as grades or shipping levels. Suppose the minimum score thresholds 0, 50, 70, and 90 are in A2:A5, and the matching labels Needs review, Pass, Merit, and Distinction are in B2:B5. If H2 contains 84, use:

=XLOOKUP(H2,A2:A5,B2:B5,"Below range",-1)

The result is Merit, because 70 is the largest threshold that is less than or equal to 84. Match mode -1 means exact match or next smaller item. For this kind of threshold table, keep the thresholds in ascending order so the band values are easy to verify. Match mode 1 instead means exact match or next larger item; it can suit a table whose entries represent upper limits. Check the table design before choosing a direction, because “next smaller” and “next larger” mean different things depending on what each number represents.

Match on More Than One Condition

XLOOKUP can combine conditions when a single field is not enough to identify a row. Suppose surnames are in A2:A6, departments in B2:B6, and employee names in C2:C6. Cell H2 contains the surname Lee and H3 contains Support. To return the name for the row meeting both conditions, use:

=XLOOKUP(1,(A2:A6=H2)*(B2:B6=H3),C2:C6,"No match")

If the Lee and Support row has Alex Lee in column C, the result is Alex Lee. Each comparison creates a series of TRUE or FALSE values; multiplying the two comparison arrays treats a row meeting both tests as 1. XLOOKUP then searches for 1 and returns the corresponding employee name. This approach requires the condition ranges and return range to have matching dimensions. If duplicate rows meet both conditions, the default search returns the first one, so use a unique key or reverse search when the data calls for a different match.

Return Several Columns at Once

When a match should return multiple adjacent fields, the return array can cover more than one column. Suppose IDs are in A2:A5, and product name, category, and price are in B2:D5. With the requested ID in F2, enter this formula in G2:

=XLOOKUP(F2,A2:A5,B2:D5,"Not found")

If F2 contains the ID from the third data row, the formula returns that row’s name, category, and price across three cells, beginning at G2. In Microsoft 365, the results spill into neighboring cells automatically. Keep those cells empty so the returned values have room to appear; otherwise Excel may report a #SPILL! error. This is a convenient way to populate a compact lookup panel without writing a separate formula for each field.

Common XLOOKUP Problems and Fixes

#N/A: No exact match was found and no fallback text was supplied. Check for extra spaces, different data types, misspellings, or an ID that is not in the lookup range. A number stored as text may not match a numeric value.
#VALUE!: The lookup and return arrays may have incompatible dimensions. Confirm that the ranges cover corresponding rows or columns.
#SPILL!: A formula returning several values cannot place them because cells in the spill area are occupied, merged, or otherwise unavailable. Clear the expected output cells.
Unexpected result among duplicates: The default is the first match from the beginning. Use search_mode -1 to return the last match, or improve the data so the lookup key is unique.
Formula not recognized: The workbook may be open in an Excel version earlier than Excel 2021, or another spreadsheet program that does not support XLOOKUP. Check the software and version before replacing the formula.

When typing a formula, use commas between arguments in standard English-language Excel settings. Some regional configurations use semicolons as list separators; if Excel rejects a formula that otherwise looks correct, your regional separator setting may be the reason. On Windows, Microsoft 365 uses the same XLOOKUP arguments described here. XLOOKUP is also available in supported Microsoft 365 versions for Mac and Excel for the web, though menus and some interface details can differ. The function’s core syntax and behavior are the same.

XLOOKUP Compared with VLOOKUP and INDEX/MATCH

VLOOKUP remains common in older workbooks, but its lookup column must be the first column of its table array and it returns a column to the right. It also uses approximate matching by default unless its final argument requests an exact match. XLOOKUP searches a separate range, returns from a separate range, defaults to exact matching, and can search in either direction. That makes many everyday lookups shorter and less fragile if columns are inserted into the source data.

INDEX and MATCH can also perform flexible lookups, including searches to the left, and remain useful for compatibility with older Excel releases. XLOOKUP is often simpler to read when available, but it does not automatically make a poorly structured list reliable. Unique identifiers, consistent data types, clear handling of missing values, and correctly aligned ranges still matter whichever function you choose.

Frequently Asked Questions

Does XLOOKUP use exact match by default?
Yes. Its default match mode is exact match, and its default search direction is first-to-last. You can specify other modes when your data needs them.

Can XLOOKUP return a value from a column to the left?
Yes. Set the lookup array to the column containing the value to find and the return array to the column containing the answer. The return column can be on either side.

Why does XLOOKUP return #N/A?
Usually, the lookup value is not an exact match in the lookup range. Check for missing records, spaces, spelling differences, and numbers stored as text. You can also supply an if-not-found result for expected missing values.

Can XLOOKUP return multiple columns?
Yes. Use a multi-column return array. In Microsoft 365 and other supported dynamic-array versions, the matching row’s results spill into adjacent cells, which must be clear.

Is XLOOKUP available in Excel 2019?
No. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not Excel 2019. For older versions, use a compatible alternative such as INDEX/MATCH or an exact-match VLOOKUP where its left-to-right layout fits.

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.