Excel XLOOKUP Examples for Common Spreadsheet Tasks

Microsoft Excel’s XLOOKUP finds a value in one range and returns the corresponding value from another. For example, with product IDs in A2:A5 and names in B2:B5, enter =XLOOKUP(E2,A2:A5,B2:B5,"Not found") to look up the ID in E2 and return its product name, or “Not found” if there is no match. The examples below show exact, approximate, reverse, wildcard, and two-way lookups, plus ways to handle common errors.

What XLOOKUP Does

XLOOKUP is a modern lookup function available in Microsoft 365 and Excel 2021 or later. It searches a lookup array for a chosen value, then returns a value from the corresponding position in a separate return array. The search and return ranges do not have to be adjacent, and the return range can be to the left or right of the search range.

Its syntax is =XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]). The first three arguments are required; the arguments in square brackets are optional. By default, XLOOKUP looks for an exact match and searches from the first item to the last.

lookup_value is the item to find, such as a product code or employee name.
lookup_array is the single row or column Excel searches.
return_array contains the corresponding result or results.
if_not_found supplies a message or value if the search finds no match.
match_mode selects exact, approximate, or wildcard matching.
search_mode controls search direction and, in one mode, binary searching.

Keeping the lookup and return ranges the same height or width helps avoid errors. For a vertical search, use vertical ranges; for a horizontal search, use horizontal ranges. Unlike older lookup approaches, you do not need to count a return-column index or rearrange a table just to retrieve a value from its left side.

Basic Exact-Match Example

Exact match is the usual choice for IDs, product codes, names, and other values that should match precisely. Suppose A2:A5 contains the IDs 101, 102, 103, and 104, while B2:B5 contains Mug, Notebook, Desk lamp, and Cable. Put the ID to search for in E2.

With IDs in A2:A5, product names in B2:B5, and the requested ID in E2, use =XLOOKUP(E2,A2:A5,B2:B5). If E2 contains 103, the result is Desk lamp. Because exact matching is the default, you do not need to add a match-mode argument.

Adding a not-found message makes a worksheet easier to interpret. With the same ranges and E2 containing 999, =XLOOKUP(E2,A2:A5,B2:B5,"ID not found") returns ID not found, rather than the default #N/A error. This is especially useful in reports where a missing record is an expected possibility, not necessarily a calculation failure.

The fourth argument is specific to a failed lookup. It does not replace a valid result that happens to be blank, and it does not catch every possible formula error. For example, a misspelled range reference can still produce a different error that needs to be fixed at its source.

Return Values to the Left or Multiple Columns

XLOOKUP does not require the return range to sit to the right of the lookup range. That makes it convenient when a table’s identifier is not in its first column. Suppose product names are in A2:A5 and their codes are in B2:B5. To enter a code in E2 and retrieve the name, search the code column and return the name column.

With names in A2:A5, codes in B2:B5, and the code to find in E2, enter =XLOOKUP(E2,B2:B5,A2:A5,"Code not found"). If E2 contains a code associated with Desk lamp, the formula returns Desk lamp. The order of the ranges in the formula—not their physical left-to-right placement—determines what Excel searches and returns.

XLOOKUP can also return more than one adjacent result at once. Suppose IDs are in A2:A5, names in B2:B5, departments in C2:C5, and prices in D2:D5. With the requested ID in F2, =XLOOKUP(F2,A2:A5,B2:D5,"ID not found") returns the matching name, department, and price across three cells. In Microsoft 365 or Excel 2021 and later, the values spill into neighboring cells automatically. Make sure those cells are empty; otherwise Excel may show a #SPILL! error.

Approximate Matches for Bands and Thresholds

Approximate matching is useful when the lookup value falls into a range, such as a score-to-grade table or a shipping-rate threshold. For a “next smaller item” match, set match_mode to -1. The thresholds should be arranged in ascending order so the intended lower boundary is easy to identify.

Suppose A2:A6 contains score thresholds 0, 60, 70, 80, and 90, and B2:B6 contains the grades F, D, C, B, and A. With a score in E2, use =XLOOKUP(E2,A2:A6,B2:B6,"Below range",-1). If E2 is 86, Excel finds the next smaller threshold, 80, and returns B. A score below 0 produces “Below range” because no exact or smaller threshold exists.

For the next larger item instead, use match_mode 1. With the same thresholds and grades, =XLOOKUP(E2,A2:A6,B2:B6,"Above range",1) returns A for a score of 86, because 90 is the next larger threshold. That is a different grading rule from choosing the highest threshold the score has already reached, so choose the mode that matches the table’s meaning. Do not confuse approximate match modes with the separate search_mode argument.

Wildcard Searches for Partial Text

When you know only part of a text value, XLOOKUP can use wildcards if match_mode is set to 2. The asterisk represents any number of characters, and the question mark represents one character. A tilde can escape a wildcard when you need to search for a literal asterisk or question mark.

Suppose A2:A5 contains Anderson, Patel, Johnson, and Garcia, and B2:B5 contains their departments. To find the first name ending in “son,” enter =XLOOKUP("*son",A2:A5,B2:B5,"No match",2). XLOOKUP returns the department for Anderson, the first matching name in the range. If you want to search for a phrase anywhere in a value, surround it with asterisks—for example, a criterion like "*lamp*" can match text containing “lamp.”

Wildcard matching is for text patterns, not a substitute for approximate numeric matching. It also returns the first matching record by default, so a partial-text search may not uniquely identify a person or item. If several entries could match, refine the search term or use a more specific key.

Search from the Bottom and Handle Duplicates

By default, XLOOKUP returns the first matching item from the top of a vertical range. Set search_mode to -1 to search from the last item upward and return the final match. This is handy when a log is ordered by date and you want the latest row for a category, provided the rows are in the order you intend.

Suppose A2:A5 contains East, West, East, and North, while B2:B5 contains 120, 80, 150, and 60. With “East” in E2, use =XLOOKUP(E2,A2:A5,B2:B5,"No match",0,-1). The result is 150, the value in the last row where East appears. The explicit 0 requests an exact match, and -1 in the sixth argument reverses the search direction.

Searching from the bottom is not the same as sorting the records or checking which date is chronologically latest. If rows are not in date order, the last occurrence in the range may not be the most recent date. For that situation, sort or otherwise identify the row with the latest date before relying on row position.

Two-Way Lookup by Row and Column

A two-way lookup selects a row and a column, such as the sales amount for a particular employee in a particular quarter. XLOOKUP can be nested: one lookup selects the relevant column of data, and another selects the requested row from that column.

Suppose B1:D1 contains Q1, Q2, and Q3; A2:A4 contains Mia, Noah, and Olivia; and B2:D4 contains their quarterly values. Put the employee name in F2 and the quarter in G2. With those ranges and inputs, enter =XLOOKUP(F2,A2:A4,XLOOKUP(G2,B1:D1,B2:D4)). If Mia’s Q2 value is 18 and F2 contains Mia while G2 contains Q2, the result is 18.

Here, the inner lookup finds the quarter heading and returns the matching data column; the outer lookup then finds the employee in that column. Check that the headings and names are unique and that the ranges cover the same table. If a heading is absent, the inner lookup returns #N/A unless you provide its own not-found argument.

Common Problems and How to Fix Them

#N/A appears unexpectedly: Check for extra spaces, different text, or a number stored as text in one range but as a number in the other. The displayed values may look alike while Excel treats them as different.
The wrong duplicate is returned: The default search returns the first match. Use search_mode -1 for the last match, or make the lookup key more specific.
#VALUE! appears: Confirm that the lookup and return arrays have compatible dimensions. A vertical lookup range paired with a return range of an unrelated size can cause problems.
#SPILL! appears: A multi-column or multi-row result needs empty cells in its spill area. Clear obstructing cells or move the formula.
The function name is not recognized: XLOOKUP is not available in Excel 2019 or Excel 2016. Open the workbook in Microsoft 365 or Excel 2021 or later, or use an older-compatible alternative.

When a lookup might fail for several reasons, do not automatically hide every error with a broad error-handling formula. A not-found message can be useful for a genuinely absent key, but masking errors caused by incorrect ranges or data can make a workbook harder to audit.

XLOOKUP Compared with VLOOKUP

VLOOKUP remains familiar and can be necessary when a workbook must work in older Excel versions, but it has different constraints. VLOOKUP searches in the first column of its table array and returns a value from a column to its right by using a numeric column index. XLOOKUP accepts separate lookup and return arrays, can return values from either side, and uses exact match by default.

That default is a meaningful difference: VLOOKUP’s fourth argument can be omitted, in which case it uses approximate matching under its documented conditions. For exact matches with VLOOKUP, users commonly specify FALSE as the fourth argument. XLOOKUP also offers a built-in not-found argument and a reverse search option. Choose based on the workbook’s compatibility needs as well as convenience; a recipient using Excel 2019 or earlier may not be able to calculate XLOOKUP formulas.

Frequently Asked Questions

Is XLOOKUP available in every version of Excel?
XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. It is not included in Excel 2019 or Excel 2016. If you share a workbook with someone using an older version, check compatibility before replacing established lookup formulas.

Does XLOOKUP use exact match by default?
Yes. Unless you specify another match_mode, XLOOKUP searches for an exact match. This differs from the behavior many users encounter when they omit VLOOKUP’s final argument.

Can XLOOKUP return a value from a column on the left?
Yes. Set the lookup_array to the column containing the key and the return_array to the column containing the answer. The return range can be physically left or right of the lookup range.

How do I return the last matching value?
Set the sixth argument, search_mode, to -1. XLOOKUP then searches from the last item toward the first and returns the last match in the selected range.

Can one XLOOKUP return several columns?
Yes. Use a multi-column return_array, such as B2:D5, with a single lookup value. In Microsoft 365 and Excel 2021 or later, the matching row can spill into adjacent cells, which must be clear for the result to display.

Does XLOOKUP work on Mac or Excel for the web?
XLOOKUP is available in supported Microsoft 365 versions of Excel for Mac and Excel for the web as well as Windows. The examples use the same function logic; interface details can differ, and formula argument separators may depend on regional settings.

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.