XLOOKUP Examples: Formulas for Everyday Excel Tasks

To return a value for a matching ID, use XLOOKUP with the value to find, the lookup range, and the range containing the answer. With product IDs in A2:A5, prices in B2:B5, and the ID to find in E2, =XLOOKUP(E2,A2:A5,B2:B5) returns $18.50 when E2 contains P-103.

How XLOOKUP works

XLOOKUP searches one range for a value and returns a corresponding value from another range. Its basic pattern is lookup value, lookup array, return array, followed optionally by a not-found message, a match mode, and a search mode. Unlike older lookup formulas, the lookup range and return range do not need to be arranged in a particular left-to-right order.

The function uses exact matching by default and searches from the first item to the last. That makes a short formula suitable for many everyday tasks. The lookup and return ranges should have compatible dimensions: for a vertical lookup, both are typically the same number of rows; for a horizontal lookup, both are the same number of columns.

In Microsoft Excel for Microsoft 365 on Windows, enter a formula in a cell and press Enter. If a formula returns several values, Excel may place them in neighboring cells automatically; this is called a spilled array. XLOOKUP is also available in Excel 2021 and Microsoft 365 editions for Mac and the web, though available features can depend on the specific edition and update status.

Find an exact match

A common use is looking up a price, department, or status from a reference table. Suppose A2:A5 contains P-101, P-102, P-103, and P-104, while B2:B5 contains $12.00, $16.75, $18.50, and $25.00. With the requested product ID in E2, =XLOOKUP(E2,A2:A5,B2:B5) returns $18.50 for P-103. The formula returns the value on the same row as the match.

This exact-match behavior is useful for IDs, names, invoice numbers, and other values that should identify one specific record. It is generally safer than an approximate lookup for identifiers: if an ID is missing, you want Excel to report that rather than silently select a nearby value. Exact matching is already the default, so there is no need to add a match-mode argument for this basic case.

For an employee directory, for example, the lookup value might be an employee number and the return array might contain email addresses. Keep the lookup and return ranges aligned to the same records, and use a cell reference such as E2 for the searched value so the formula can be copied down for a list of requests.

Show a useful message or look to the left

If a searched value may not exist, the fourth argument lets you replace the usual not-found result with a message. Using product IDs in A2:A5, prices in B2:B5, and the requested ID in E2, =XLOOKUP(E2,A2:A5,B2:B5,"ID not found") displays ID not found when E2 contains P-999. If E2 contains P-103, it still returns $18.50. This message is useful in reports because it tells a reader what happened without requiring them to interpret an error.

XLOOKUP can also return a value from a column to the left of the lookup column. Suppose employee names are in A2:A5, employee IDs are in B2:B5, and the ID to search is in E2. The formula =XLOOKUP(E2,B2:B5,A2:A5,"No employee") returns the name on the matching row, such as Mina Patel for ID 204. The lookup array is B2:B5 and the return array is A2:A5; the return range does not have to sit to the right.

This flexibility is one reason XLOOKUP can replace many VLOOKUP formulas. With VLOOKUP, the return column normally has to be to the right of the lookup column, and inserting or moving columns can make its numbered column argument harder to maintain.

Use approximate matches for tiers and ranges

Approximate matching is appropriate when the table describes thresholds, such as commission bands, shipping levels, or grade cutoffs. Assume A2:A5 contains the ascending sales thresholds 0, 1000, 5000, and 10000, and B2:B5 contains the corresponding rates 0%, 2%, 4%, and 6%. With sales of 7200 in E2, =XLOOKUP(E2,A2:A5,B2:B5,, -1) returns 4%, the rate for the largest threshold that is less than or equal to 7200.

The fifth argument, match mode, controls this behavior. A value of 0 requests an exact match; -1 requests an exact match or the next smaller item; 1 requests an exact match or the next larger item; and 2 enables wildcard matching. The omitted fourth argument in the example leaves the not-found message unspecified, while the fifth argument sets the match mode. The comma before -1 is necessary to leave that fourth argument blank.

For tier lookups, check that the thresholds are correct and in the intended order. A mistaken threshold or an unsorted table can give a result that looks plausible but applies the wrong tier. If the searched amount is below the smallest threshold, there may be no next-smaller match; decide whether to supply a not-found message or define a suitable starting threshold.

Return the last match or use wildcards

When a lookup value appears more than once, the default search returns the first match. To return the last matching row instead, use the sixth argument to search from the bottom upward. Suppose repeated order IDs are in A2:A6, their statuses are in C2:C6, and the requested order ID is in E2. The formula =XLOOKUP(E2,A2:A6,C2:C6,"No record",0,-1) returns the status from the last row containing that ID. This is useful when later rows represent updates, but it returns the last row position—not necessarily the most recent date unless the data is arranged chronologically.

Wildcards can help find text when only part of a name is known. With names in A2:A5, email addresses in B2:B5, and Jo* in E2, =XLOOKUP(E2,A2:A5,B2:B5,"No match",2) returns the email for the first name beginning with Jo. In wildcard mode, an asterisk represents any number of characters and a question mark represents one character. Use a tilde before a wildcard character when you need to search for the character itself.

Wildcard matching is not the same as approximate numeric matching. It is intended for text patterns, and the default search still returns the first qualifying match. If several names begin with the same letters, make the pattern more specific or use a different method to return all matching records.

Return several columns or look up a row and column

XLOOKUP can return multiple adjacent columns from the matching record. Suppose product IDs are in A2:A5, product descriptions and prices are in B2:C5, and the requested ID is in F2. The formula =XLOOKUP(F2,A2:A5,B2:C5,"Not found") returns both the description and price for the matching product, spilling the two results into neighboring cells. Leave those destination cells empty; otherwise Excel may show a #SPILL! error because it cannot place the full result.

A two-way lookup can combine two XLOOKUP functions. Assume product names are in A2:A4, quarter headings are in B1:D1, sales values are in B2:D4, the product to find is in F2, and the quarter is in G2. The formula =XLOOKUP(F2,A2:A4,XLOOKUP(G2,B1:D1,B2:D4)) first selects the column for the requested quarter, then finds the product in that column. If Laptop is in row 2 and Q2 is the second quarter heading, and the value at their intersection is 14200, the formula returns 14200.

Two-way lookups are useful for schedules, budgets, and reports where one label identifies a row and another identifies a column. Make sure the row labels, headings, and values correspond to the same table. For a changing report with many possible results, other dynamic-array functions may be a better fit than nesting additional lookups.

Common problems and how to fix them

#N/A: Excel did not find a qualifying match. Check spelling, extra spaces, and whether numbers are stored as numbers in one range but as text in the other. Add the not-found argument if you want a clearer message.
Wrong result from duplicates: XLOOKUP returns the first match by default. Use a reverse search when the last row is the desired record, or remove duplicates if the lookup value should be unique.
#VALUE! or mismatched results: Confirm that the lookup and return arrays have compatible shapes and cover the same records. A lookup range with one row for each record should not be paired with an unrelated return range.
#SPILL!: A multi-column result needs empty cells in which to appear. Clear obstructing cells, or choose a single-column return range if only one result is needed.
Approximate result seems incorrect: Review the match mode and threshold order. Exact matching is the default; approximate modes should be chosen deliberately and tested against values at, between, and outside the thresholds.

Also check for hidden spaces or inconsistent punctuation in imported data. Cleaning the source values can be more reliable than making every lookup formula compensate for inconsistent entries. When reviewing a workbook, test both a value known to exist and one known to be absent so the match and not-found behavior are clear.

XLOOKUP compared with older lookup methods

VLOOKUP remains familiar and is available in older Excel versions, but it searches the first column of a table and returns from a column to its right. Its column number is easy to miscount and can become stale after a structural change. XLOOKUP names the lookup and return ranges separately, can search left or right, and has a built-in not-found argument.

INDEX with MATCH can also perform flexible lookups, including leftward searches, and may be useful when sharing workbooks with people using Excel versions that do not support XLOOKUP. However, it typically requires a combination of functions rather than one lookup function. XLOOKUP is not available in Excel 2019 or Excel 2016, so a workbook using it may not calculate correctly for someone limited to those releases. If compatibility matters, confirm the recipients’ Excel versions before choosing a formula.

XLOOKUP finds a matching record; it does not automatically filter a whole table to show every row meeting a condition. In Microsoft 365 and Excel 2021, FILTER is an option for returning all rows that meet a condition. Use a lookup when you want a corresponding value for a key, and a filtering function when the task is to display a set of matching records.

Frequently Asked Questions

Is XLOOKUP exact match by default?
Yes. Its default match mode is exact, and its default search direction is first to last. You can specify a different match mode or search direction when the task requires one.

Can XLOOKUP search from right to left?
Yes. Choose the lookup array and return array separately, in either order. For example, search an ID column and return a name from a column to its left.

Why does XLOOKUP return #N/A?
Usually the lookup value is absent or differs from the table value because of a typo, extra spaces, or inconsistent data types. You can provide a not-found message as the fourth argument, but check the underlying data if a match should exist.

Can XLOOKUP return more than one column?
Yes. Use a multi-column return array, such as B2:D5, and Excel can spill the matching row’s results across adjacent cells. Make sure the spill area is clear.

Does XLOOKUP work in Excel 2019?
No. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not Excel 2019 or Excel 2016. For older versions, consider compatible alternatives such as VLOOKUP or INDEX with MATCH.

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.