XLOOKUP Examples for Beginners: Formulas, Tips, and Fixes

Formulas & functions · Updated November 3, 2026

XLOOKUP finds a value in one range and returns the corresponding value from another; for example, with product codes in A2:A5, names in B2:B5, and the code to find in D2, enter =XLOOKUP(D2,A2:A5,B2:B5,"Not found"). If D2 contains P-106, the result is Notebook. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, and the examples below explain how to adapt it to everyday lookup tasks.

What XLOOKUP does

A lookup formula connects a value you know, such as an employee ID or product code, to information you want, such as a name or price. XLOOKUP searches a lookup array and returns a corresponding value from a return array. You choose those ranges separately, which makes the formula easier to read and less restrictive than older lookup methods.

For an exact match, which is the usual choice for IDs, product codes, and names, XLOOKUP searches from the first item to the last by default. It does not require the lookup column to be sorted. It can also search in either direction, return a value from a column to the left of the lookup column, and display a custom message when it finds no match.

In Excel for Microsoft 365 on Windows, type a formula in a cell and press Enter. XLOOKUP works in Excel for Mac and Excel for the web as well; the core formula examples are the same. In some regional settings, Excel uses semicolons rather than commas between formula arguments, so follow the separator style already used by your Excel installation.

Start with an exact-match lookup

Suppose a small product list has codes in A2:A5 and product names in B2:B5: P-104 is Desk lamp, P-105 is Cable, P-106 is Notebook, and P-107 is Mouse. Enter the code to find in D2. The formula below searches the code range and returns the name from the same row.

With product codes in A2:A5, product names in B2:B5, and the code to search for in D2, enter:

=XLOOKUP(D2,A2:A5,B2:B5,"Not found")

If D2 contains P-106, the result is Notebook. The first argument is the value to find, the second is where Excel should search, and the third is the range containing the answer. The fourth argument, “Not found”, tells Excel what to display if there is no match. Leaving that argument out is allowed, but a missing match then returns the #N/A error.

Use ranges that cover corresponding records and keep their dimensions aligned. For example, a five-row lookup range should normally be paired with a five-row return range. If you copy the formula down a column, consider locking the data ranges with dollar signs so they stay fixed while the lookup cell changes. For example, A2:A5 can be written as $A$2:$A$5.

Show a helpful result when nothing matches

A custom not-found message can make a report easier to understand than a raw error. It is also useful when a lookup value may be missing because someone has not yet added that record to the source list. Choose wording that distinguishes a genuine missing record from other formula problems.

With employee IDs in A2:A5, employee names in B2:B5, and the ID to check in D2, use:

=XLOOKUP(D2,A2:A5,B2:B5,"ID not listed")

If D2 contains E-205 and that ID is absent from A2:A5, the result is ID not listed. If E-205 is present, Excel returns the name on its row instead. This fourth argument handles a value that XLOOKUP cannot find; it does not hide every possible error, such as a broken range reference or incompatible data.

A common reason for an unexpected not-found result is that one value is stored as text while the other is a number. Extra spaces and visually similar characters can also interfere with matching. Check the source and lookup values before changing the formula, especially when the data was imported from another system.

Look to the left with a reverse lookup

Unlike VLOOKUP, XLOOKUP does not require the return column to sit to the right of the lookup column. You simply put the lookup range and return range in the order that matches your task. This is useful when an employee name is in one column and the employee ID you need is immediately to its left.

With employee names in A2:A5, IDs in B2:B5, and the name to find in D2, enter:

=XLOOKUP(D2,A2:A5,B2:B5,"Name not listed")

If A2:A5 contains Ana Ruiz, Ben Cole, Chris Park, and Dana Lee, and B2:B5 contains E-201, E-202, E-203, and E-204 in the same row order, looking up Chris Park returns E-203. The formula still searches the first range and returns from the second; it does not matter that the answer is physically to the right in this particular example. To search in the ID column and return a name to its left, use the ID range second and name range third.

Return more than one column

XLOOKUP can return multiple adjacent values from one matching row in Excel versions that support dynamic arrays, including Microsoft 365 and Excel 2021 or later. This can save you from writing a separate lookup for each field. The returned values spill into neighboring cells, so keep those destination cells empty.

Suppose IDs are in A2:A4, product names are in B2:B4, prices are in C2:C4, and the ID to find is in E2. Use:

=XLOOKUP(E2,A2:A4,B2:C4,"Not found")

If the row for ID 102 contains Notebook in column B and 4.50 in column C, and E2 contains 102, the formula returns Notebook in the formula cell and 4.50 in the next cell. The return range includes two columns, so the answer occupies two cells. If Excel displays a spill error, check that the cells where the results need to appear are not occupied or merged.

Use approximate matches for ranges and thresholds

Approximate matching is useful when a value belongs to a band, such as a score range or a shipping bracket. For the match mode, -1 means an exact match or, if there is none, the next smaller value. Mode 1 means an exact match or the next larger value. For a next-smaller lookup, sort the thresholds from smallest to largest so the intended band is clear.

Suppose A2:A5 contains the ascending score thresholds 0, 60, 70, and 80, and B2:B5 contains Needs work, Pass, Good, and Excellent. Put a score in D2. With 76 in D2, use:

=XLOOKUP(D2,A2:A5,B2:B5,"No score",-1)

The result is Good: 70 is the largest threshold not greater than 76. At a score of 80, the result is Excellent because there is an exact threshold. At a score below 0, the formula returns No score because there is no exact or smaller threshold. Check your boundary choices carefully; the formula applies the thresholds you provide and does not decide what a grading policy should be.

Find text with wildcards

If you know only part of a text value, XLOOKUP can use wildcard matching. Set match mode to 2 to enable wildcards. An asterisk (*) can stand for any number of characters, and a question mark (?) stands for one character. A tilde (~) lets you search for a literal asterisk or question mark rather than treating it as a wildcard.

With descriptions in A2:A4, matching return values in B2:B4, and the search pattern shown in the formula, use:

=XLOOKUP("*mug*",A2:A4,B2:B4,"No match",2)

If A2 is Travel mug, A3 is Ceramic plate, and A4 is Mug lid, this formula returns the value from B2 because Travel mug is the first match. Wildcards are for text matching; they are not a way to find an approximate numeric value. If several descriptions match, ordinary search order means the first matching row is returned.

Return the last match instead of the first

When a lookup value appears more than once, the default search returns its first occurrence. That may not be the record you want in a log where later rows contain more recent updates. Set the search mode to -1 to search from the last item toward the first. This changes search direction, not match type.

With transaction codes in A2:A6, statuses in B2:B6, and the code to find in D2, use:

=XLOOKUP(D2,A2:A6,B2:B6,"No record",0,-1)

If A2:A6 contains T-8, T-9, T-8, T-10, T-8, and the corresponding statuses in B2:B6 are Open, Open, Review, Closed, Complete, searching for T-8 in D2 returns Complete from the last row. The 0 explicitly requests an exact match; -1 tells Excel to search from bottom to top. For a very large range, reverse searching can also be useful when recent records are at the bottom, although a well-structured data model is still important.

Common XLOOKUP problems and fixes

When a formula does not return the expected value, check the lookup value, the selected ranges, and the match settings before rebuilding it. These are frequent causes of confusion:

  • #N/A: No match was found, or the formula omitted a custom not-found result. Confirm that the value exists and that text, numbers, spaces, and punctuation match.
  • Wrong row: The lookup and return ranges may start on different rows or include different records. Make sure their positions correspond.
  • Wrong duplicate: The default finds the first match. Use search mode -1 if the last match is the one you need.
  • Spill error: A multi-cell result cannot fill its destination because one or more spill cells are occupied. Clear the obstructing cells.
  • Function name is not recognized: The workbook may be open in an older Excel version without XLOOKUP. Excel 2016 and Excel 2019 do not include it; use a compatible alternative or open the file in Microsoft 365 or Excel 2021 or later.

For formulas copied down a table, use absolute references for fixed source ranges and a relative reference for the changing lookup value. For example, a lookup cell reference like D2 normally changes to D3 when filled down, while $A$2:$A$100 remains fixed. Structured table references can also make formulas easier to maintain when a list grows, but the basic range examples are often simpler for learning the function.

XLOOKUP compared with VLOOKUP and INDEX/MATCH

VLOOKUP is familiar and remains useful in older workbooks, but it searches the first column of a table and returns a value from a column to its right. Its column number is also a number you must count, so inserting a column can make a formula return the wrong field unless the formula is adjusted. XLOOKUP names the lookup and return ranges directly and can return from either side.

INDEX/MATCH is another established way to look up values and remains useful in older Excel versions. It can handle lookups in either direction, but it generally takes two functions and can be less approachable for a beginner. XLOOKUP is not automatically a better choice for every workbook: if a file must be used in Excel 2019 or earlier, compatibility matters, and an older lookup method may be more practical.

One important misconception is that XLOOKUP always returns a unique result. It returns one matching item, by default the first one found, even if several rows contain the same lookup value. If duplicates are meaningful, decide whether the first or last record is appropriate, or use a method designed to return all matching rows.

Frequently Asked Questions

Is XLOOKUP exact match by default?
Yes. Its default match mode is exact match, so you usually do not need to specify a match mode for IDs, product codes, or names. Approximate and wildcard matching require different settings.

Why does XLOOKUP return #N/A?
Usually, Excel cannot find the lookup value in the selected range. Check for mismatched text and numbers, extra spaces, misspellings, and incorrect range references. You can provide a custom not-found message as the fourth argument.

Can XLOOKUP search to the left?
Yes. Set the lookup array to the range containing the value to find and the return array to the range containing the answer. The return range can be physically to the left or right of the lookup range.

Does XLOOKUP work in Excel 2019?
No. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in Excel 2019 or Excel 2016. A workbook containing XLOOKUP may not calculate correctly in those older versions.

How do I make XLOOKUP return the last match?
Set its final search-mode argument to -1. That tells Excel to search from the last item toward the first. It does not change whether the match is exact or approximate.

Related

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.