Excel XLOOKUP Practice Examples: Formulas and Tips

Excel XLOOKUP practice examples are easiest to learn by matching a lookup value to a column and returning a related result with a formula such as =XLOOKUP(E2,A2:A6,C2:C6,"Not found"). The examples below cover exact matches, missing values, duplicates, multiple conditions, approximate matches, two-way lookups, and returning several columns.

What XLOOKUP does and which Excel versions support it

XLOOKUP searches one range or array for a value and returns the corresponding value from another range or array. Unlike older VLOOKUP formulas, the return range does not have to sit to the right of the lookup range. You can also choose what to show when there is no match, set an approximate-match rule, and search from the bottom up.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including supported versions of Excel for Mac and Excel for the web. It is not built into Excel 2019 or Excel 2016. If a workbook containing XLOOKUP is opened in an older version, that version may show a #NAME? error instead of calculating the formula.

The basic syntax is =XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]). The first three arguments are required. Brackets in this syntax indicate optional arguments; do not type the brackets into a formula. In Microsoft 365 for Windows, formulas generally use commas between arguments under the standard regional settings. Some regional settings use semicolons instead.

Practice 1: Find an exact match

Suppose a product list has SKUs in A2:A6, product names in B2:B6, and prices in C2:C6. The rows contain P-104 / Cable / 8.5, P-205 / Mouse / 16, P-310 / Desk lamp / 24.5, P-412 / Notebook / 4, and P-518 / Keyboard / 32. Put the SKU P-310 in E2. With SKUs in A2:A6 and prices in C2:C6, enter:

=XLOOKUP(E2,A2:A6,C2:C6,"Not found")

The result is 24.5, which can be displayed as $24.50 by applying a currency number format. XLOOKUP uses exact matching by default, so the formula does not need a separate match-mode argument for this everyday task. If E2 contains P-999, it returns the text Not found rather than an error.

Keeping the lookup value in a separate cell makes a practice sheet easier to reuse: change E2 to another SKU and the result updates. Use ranges of the same height for the lookup and return arrays. If the lookup array has five rows but the return array has four, XLOOKUP cannot pair the rows reliably and returns an error.

Practice 2: Return a message when there is no match

The fourth argument controls what appears when XLOOKUP cannot find the value. You can use a short message, a blank, or another useful indicator. With the same SKU and price layout as the first example, and a search value in E2, this formula returns an empty string if that SKU is absent:

=XLOOKUP(E2,A2:A6,C2:C6,"")

The displayed result looks blank, but the formula still occupies the cell. That distinction matters if other formulas test the result: a truly empty cell and a formula returning an empty string are not identical in every Excel operation. For an audit list, a clear message such as “SKU not listed” is often safer than a blank because it makes missing data visible.

Do not use if_not_found as a substitute for diagnosing every error. It handles the no-match condition, but it will not necessarily hide problems such as a malformed formula or incompatible array dimensions. A visible message helps distinguish a valid lookup that found no item from a workbook problem.

Practice 3: Find the last matching record

When a lookup value appears more than once, the default search proceeds from the first item to the last and returns the first match. A reverse search is useful for a log where newer records are appended below older ones. Imagine transaction IDs in A2:A7 and status values in B2:B7, with ID T-204 appearing in rows 3 and 7. With IDs in A2:A7, statuses in B2:B7, and T-204 in E2, use:

=XLOOKUP(E2,A2:A7,B2:B7,"Not found",0,-1)

The result is the status from row 7 because the final argument, -1, tells XLOOKUP to search from last to first. The 0 explicitly requests an exact match; it is optional because exact matching is already the default. This approach returns the last matching record in the current row order. It does not determine which record is newest by date unless the rows have actually been arranged to make that true.

Practice 4: Match more than one condition

Sometimes a name alone is not unique. For example, a sales table may contain product names in A2:A6, regions in B2:B6, and sales amounts in C2:C6. Suppose its rows are Mouse / East / 90, Mouse / West / 125, Cable / West / 40, Lamp / East / 70, and Mouse / East / 95. Put Mouse in H2 and West in H3. With product names in A2:A6, regions in B2:B6, and amounts in C2:C6, enter:

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

The result is 125. Each comparison creates a series of TRUE or FALSE values, and multiplying the two series makes a 1 where both conditions are true and a 0 elsewhere. XLOOKUP searches for 1 and returns the amount from the same row. If multiple rows meet both conditions, it returns the first one in the search direction; this formula does not add all matching sales. Use a summing function instead when the task is to total every qualifying row.

Practice 5: Use a wildcard to find text

For a partial text search, set match_mode to 2. A question mark matches one character, and an asterisk matches any number of characters, including none. Suppose A2:A5 contains Desk lamp, Desk organizer, Floor lamp, and Table. Put Desk* in E2. With item names in A2:A5 and categories in B2:B5, use:

=XLOOKUP(E2,A2:A5,B2:B5,"No match",2)

The formula returns the category from the first item matching the pattern: in this list, that is the category beside Desk lamp. If you need to find an actual asterisk or question mark in the source text rather than use it as a wildcard, precede that character with a tilde. Wildcard matching is useful for inconsistent labels, but it can select an unintended first match, so check that the pattern is specific enough.

Practice 6: Find a value in a band

XLOOKUP can return a result for a threshold rather than an exact value. Suppose the minimum score thresholds in A2:A5 are 0, 60, 70, and 80, and the corresponding grades in B2:B5 are F, D, C, and B. Put a score of 76 in E2. With thresholds in A2:A5 and grades in B2:B5, use:

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

The result is C: with match mode -1, XLOOKUP seeks an exact match or, if it cannot find one, the next smaller item. Here, 70 is the largest listed threshold not above 76. If a score falls below the smallest threshold, the formula returns Below range. Match mode 1 has the complementary behavior: it seeks an exact match or the next larger item.

Make the thresholds and boundary rules fit the real task. For instance, this example treats 70 as the start of grade C and 80 as the start of grade B. Approximate-match logic can be useful for rate tables, shipping bands, or score ranges, but a boundary mistake can change the result. Test values exactly at each threshold as well as values just below and above it.

Practice 7: Return a value from a row-and-column table

A two-way lookup combines one lookup for a row with another for a column. Imagine employee names in A2:A4, month headings in B1:D1, and hours worked in B2:D4. The data rows are Alex with 152, 160, and 168 hours; Casey with 145, 154, and 162; and Jordan with 158, 166, and 171. Put Casey in H2 and March in H3. With names in A2:A4, month headings in B1:D1, and hours in B2:D4, use:

=XLOOKUP(H2,A2:A4,XLOOKUP(H3,B1:D1,B2:D4))

The inner lookup finds March and returns the March column from B2:D4. The outer lookup then finds Casey in the names and returns the value from that selected column on Casey’s row. The result is 162. This pattern is useful when both the row label and column heading can change without rewriting the formula.

Practice 8: Return several adjacent columns

XLOOKUP can return more than one column when its return array spans multiple columns. Suppose a staff table has employee IDs in A2:A5, names in B2:B5, departments in C2:C5, and locations in D2:D5. Put an ID in F2. With IDs in A2:A5 and the three return fields in B2:D5, enter:

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

In Microsoft 365 and Excel 2021 or later, the formula returns the matching name, department, and location across three cells. The values spill into cells to the right, so those cells must be empty. If one is occupied, Excel may show #SPILL!; clear the blocking cell or move the formula. In an older Excel version without dynamic arrays, this multi-column result may not behave as expected, and XLOOKUP itself is not available in Excel 2019 or 2016.

How XLOOKUP compares with VLOOKUP and INDEX/MATCH

VLOOKUP remains familiar, but its lookup column must be at the left of the return column, and its column index is a number that can become wrong when columns are inserted or rearranged. XLOOKUP names the lookup and return ranges directly, can look left or right, and includes a built-in not-found argument. These differences often make an XLOOKUP formula easier to read and maintain.

INDEX/MATCH is another established option. It can look in either direction and is supported in many older Excel releases, which may matter when a file must work for people using Excel 2016 or 2019. XLOOKUP is usually simpler for a single lookup, while compatibility requirements may favor older functions. Before sharing a workbook, confirm which Excel versions recipients use rather than assuming every installation supports the same functions.

Common XLOOKUP problems and ways to check them

Unexpected no-match result: Check for extra spaces, different text, or numbers stored as text. A displayed number and a text value that looks like a number may not match as expected.
Wrong duplicate result: The default is the first matching value. Use a reverse search when you need the last match, and confirm that row order reflects the meaning you intend.
#N/A: This commonly indicates no match when no custom not-found result is supplied. Check the lookup value and source range before changing the formula.
#SPILL!: A formula returning several cells needs room to spill. Clear cells in the output area and check for merged cells or other obstructions.
Formula not recognized: If Excel shows #NAME?, confirm that the workbook is open in Microsoft 365 or Excel 2021 or later and that the function name is spelled correctly.
Unexpected approximate match: Verify the selected match mode and test the values at range boundaries. Use exact matching when a near value would be misleading.

For a reusable workbook, put lookup and return data in clearly labeled columns, avoid including unrelated blank rows in small ranges, and consider Excel Tables when the source list will grow. Structured references in tables can expand with added records, but the table and column names in a formula must match the workbook exactly.

Frequently Asked Questions

Does XLOOKUP use exact match by default?
Yes. Its default match mode is exact match. You can leave the match-mode argument out for an ordinary exact lookup, or enter 0 to make that choice explicit.

Can XLOOKUP look to the left?
Yes. The lookup array and return array are separate arguments, so the return range can be to the left or right of the lookup range. Make sure the two ranges line up row by row.

Why does XLOOKUP return the first duplicate?
The default search direction is first-to-last, so the first matching item in the lookup range is returned. Set search_mode to -1 to search from last to first.

Is XLOOKUP available in Excel 2019?
No. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but is not built into Excel 2019 or Excel 2016. Use a compatible alternative such as INDEX/MATCH when older-version support is required.

Can XLOOKUP return multiple results?
It can return multiple columns or rows from its return array, and supported dynamic-array versions of Excel spill those results into neighboring cells. It does not automatically return every separate matching record; for that task, consider FILTER in Microsoft 365 or Excel 2021 and later.

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.