How to Use XLOOKUP in Excel: A Step-by-Step Formula Guide

Formulas & functions · Updated November 21, 2026

To retrieve a value with XLOOKUP, identify the value to search for, the range where it appears, and the range containing the result. For example, with product IDs in A2:A5, prices in C2:C5, and the ID to find in E2, enter =XLOOKUP(E2,A2:A5,C2:C5,"Not found") in F2. If E2 contains P-103 and that product’s price is 18.50, the formula returns 18.50. XLOOKUP works in Microsoft 365 and Excel 2021 or later; it is not available in Excel 2019 or Excel 2016.

What You’ll Need

  • A version of Excel that includes XLOOKUP, such as Microsoft 365 for Windows, Excel 2021, Excel 2024, or a current version of Excel for Mac or the web.
  • A worksheet with a lookup value, a range to search, and a corresponding range of results. Keep the lookup and result ranges aligned so each searched item corresponds to the correct result.
  • A blank cell for the formula. If you plan to copy the formula down, use fixed references for the data ranges.

Step-by-Step

  1. Arrange and inspect your data. Put the values you want to search in one row or one column, and place the corresponding results in an adjacent row or column. For instance, A2:A5 might contain product IDs, B2:B5 product names, and C2:C5 prices. Check that each ID is paired with the right name and price on the same row. Remove accidental blank rows within the data if they could make the list confusing. XLOOKUP can work with horizontal ranges too, but the two ranges still need to line up.
  2. Choose the lookup value. Decide whether to search for a value typed directly into the formula or, usually more usefully, a value entered in a worksheet cell. In the example, E2 contains the product ID to find. A cell reference makes it easy to look up a different item by changing E2, without editing the formula. Make sure the lookup value is the same kind of data as the values in the search range: a numeric 103 and text “103” may not match.
  3. Select the cell for the result. Click the cell where the answer should appear, such as F2. Keep that cell clear if the result might spill into neighboring cells—for example, when you return multiple columns. A spill range needs enough empty space to display every returned value.
  4. Enter the basic lookup. With the search value in E2, product IDs in A2:A5, prices in C2:C5, and F2 selected, type =XLOOKUP(E2,A2:A5,C2:C5,"Not found"). Press Enter. If E2 contains P-103 and the third product’s price is 18.50, F2 displays 18.50. The arguments mean: search for the value in E2, look for it in A2:A5, and return the value from the corresponding position in C2:C5. “Not found” is the message shown if there is no match.
  5. Use an appropriate not-found message. The fourth argument is optional, but supplying it often makes a worksheet easier to understand. With product IDs in A2:A5, prices in C2:C5, and the requested ID in E2, this version returns a clear message for a missing ID: =XLOOKUP(E2,A2:A5,C2:C5,"ID not found"). If you leave that argument out and no match exists, Excel returns #N/A. You can use a short message such as “No match,” or a blank result by entering an empty text value in quotes. Do not use a message that could be mistaken for a real data value.
  6. Copy the formula for more lookups. If E2:E10 contains IDs and you want each result beside its ID, enter the formula in F2 and fill it down. With IDs in E2:E10, product IDs in A2:A5, and prices in C2:C5, use fixed references for the lookup and return ranges: =XLOOKUP(E2,$A$2:$A$5,$C$2:$C$5,"Not found"). Select F2 and drag the fill handle down, or copy and paste into F3:F10. The E2 reference changes to E3, E4, and so on; the dollar signs keep the source ranges fixed. On Windows, Ctrl+C and Ctrl+V copy and paste. On Mac, use Command+C and Command+V.
  7. Confirm the result and adjust the match type only if needed. XLOOKUP uses exact matching by default, which is appropriate for IDs, names, and most codes. For a threshold lookup, you can change the match mode. With lower score limits 0, 60, 70, and 80 in A2:A5, corresponding grades F, D, C, and B in B2:B5, and a score of 74 in E2, enter =XLOOKUP(E2,A2:A5,B2:B5,"No grade",-1). The formula returns C because 70 is the next smaller value. For this mode, keep the threshold values in ascending order. Check a known case and a missing-value case before relying on a formula copied across a report.

XLOOKUP’s arguments are entered in this order: the value to find, the range to search, the range to return from, an optional not-found result, an optional match mode, and an optional search mode. You usually need only the first three arguments and, optionally, a not-found message. Match and search modes are useful for specific cases; leaving them out gives an exact match and a search from first to last.

Common Mistakes

  • Using ranges that do not correspond. If the lookup range has four rows but the return range starts on a different row or is arranged differently, the result may be wrong or Excel may report an error. Select ranges with matching dimensions and confirm that each position represents the same record.
  • Expecting a partial match by default. XLOOKUP normally looks for an exact match. Searching for “North” will not automatically find “North Region.” For deliberate wildcard matching, set the match mode to 2. With labels in A2:A10, results in B2:B10, and the text North in E2, the formula =XLOOKUP(E2&"*",A2:A10,B2:B10,"No match",2) returns the result for the first label that starts with North. The asterisk represents any sequence of characters. Wildcards are for text matching, not a substitute for fixing inconsistent data.
  • Forgetting that the first matching item is returned. If a search range contains duplicate IDs, the default search returns the first match from the top (or left). That may be fine, but it is not necessarily the latest record. With duplicate IDs in A2:A10, corresponding dates in B2:B10, and an ID in E2, use =XLOOKUP(E2,A2:A10,B2:B10,"Not found",0,-1) to search from the last item upward. This returns the last matching row, so check that the list’s order reflects what “last” should mean.
  • Mixing text and numbers. An ID that looks like 103 may be stored as text in one range and as a number in another. Check the cell format and actual values, not only how they look on screen. Standardize the data where possible rather than adding complicated workarounds to every lookup.
  • Copying formulas with moving source ranges. Without absolute references, the source ranges can shift as you fill a formula down. Add dollar signs to the range references when each lookup should use the same source data.

Tips

  • Return more than one column at once. XLOOKUP can return a multi-column range. With IDs in A2:A5, names and prices in B2:C5, and the lookup ID in E2, enter =XLOOKUP(E2,A2:A5,B2:C5,"Not found") in F2. If the match is on row 3, the name appears in F2 and the price in G2. Excel spills those results into adjacent cells, so keep G2 clear. This is useful when you need several fields for the same record.
  • Use the function for a horizontal lookup too. The search range and return range can run across rows rather than down columns. With month names in B1:M1, sales in B2:M2, and the month to find in O1, enter =XLOOKUP(O1,B1:M1,B2:M2,"Month not found") in O2. The formula returns the sales value under the matching month.
  • Choose a deliberate approximate match. Match mode -1 means exact match or next smaller item; mode 1 means exact match or next larger item. Mode 2 enables wildcards. For the approximate modes, understand how the list is ordered and test values that fall between thresholds. If using a binary search mode, the data must be sorted in the required order; a wrongly sorted list can produce unreliable results. Most users can leave the search mode at its default.
  • Use descriptive source headings and consistent IDs. A clearly labeled table is easier to check when someone questions a result. Avoid extra spaces, inconsistent punctuation, and duplicate keys where each ID is supposed to identify one record. Consider converting a regularly updated range into an Excel Table using Insert > Table; table references can expand as you add records, though the XLOOKUP logic remains the same.
  • Remember compatibility when sharing files. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including current Excel versions for Mac and the web. A person opening the file in Excel 2019 or 2016 may see #NAME? because that version does not recognize the function. If recipients use older Excel, arrange a compatible alternative before sharing. The formula itself is entered in a cell or the formula bar in the usual way; the essential steps are the same on Windows, Mac, and Excel for the web.

Troubleshooting

The formula returns #N/A. First confirm that the lookup value actually exists in the lookup range. Check for leading or trailing spaces, different punctuation, and text-versus-number differences. If a missing match is expected, provide a not-found message as the fourth argument. Do not hide #N/A until you know whether it indicates an ordinary missing item or a data problem.

The formula returns the wrong record. Check whether the lookup key appears more than once. The default returns the first match, so duplicates can make the answer look unexpected. Decide whether you need the first or last matching item, and check that the list order supports that choice. Also verify that the return range starts and ends at the rows corresponding to the lookup range.

Excel shows #VALUE!. Check that the lookup and return arrays have compatible shapes and cover the intended records. A vertical search range should normally be paired with a corresponding vertical return range; a horizontal range should be paired with its corresponding horizontal results. Review the commas and quotation marks in the formula, especially if it was pasted from a document that changed straight quotes into typographic quotes.

Excel shows #SPILL! or the result is blocked. This can happen when the formula returns multiple cells but a cell in the required spill area already contains data, including an invisible-looking space. Clear the cells to the right or below the formula as appropriate, or move the formula to an open area. A single-result lookup does not need a spill area.

Excel shows #NAME? or does not recognize XLOOKUP. Check the Excel version. XLOOKUP requires Microsoft 365, Excel 2021, or later; updating an older perpetual version does not necessarily add newer functions. If the workbook must work in Excel 2019 or 2016, use a lookup method supported by those versions or have recipients open it in a compatible version. Also check for a misspelled function name.

An approximate result seems inconsistent. Recheck the selected match mode and the order of the lookup values. For nearest-smaller or nearest-larger threshold matching, confirm that the thresholds are appropriate and sorted as required. If you intended an exact ID match, remove the approximate-match setting and use the default exact match.

Frequently Asked Questions

Is XLOOKUP exact match by default?
Yes. If you leave out the optional match-mode argument, XLOOKUP searches for an exact match. It does not automatically return a nearest value or a partial text match.

Can XLOOKUP look left?
Yes. Unlike VLOOKUP’s usual arrangement, XLOOKUP does not require the return range to be to the right of the lookup range. Choose the column or row containing the result as the return array, even if it is positioned to the left of the search range.

Can one XLOOKUP return multiple columns?
Yes. Select a multi-column return range, and Excel can spill the matching row of results into neighboring cells. Make sure the destination cells are empty. This behavior is available in modern Excel versions that support XLOOKUP and dynamic arrays.

How do I return a blank when there is no match?
Use an empty text value for the optional not-found argument. With IDs in A2:A5, prices in C2:C5, and the ID to find in E2, enter =XLOOKUP(E2,A2:A5,C2:C5,""). The result cell appears blank when there is no match, although the formula remains in the cell. Use this carefully: a blank can make missing data less obvious than a message such as “Not found.”

Why does XLOOKUP return the first duplicate?
The normal search direction is from first to last, so the first matching item is returned. If you specifically need the last match, set the search mode to -1. Confirm the source order first, because “last” means the last matching position in that order, not necessarily the newest record unless the data is arranged that way.

Can I use XLOOKUP in older Excel versions?
Excel 2019 and Excel 2016 do not include XLOOKUP. If a workbook needs to open and calculate correctly in those versions, use a compatible lookup approach or ensure that users open it in Microsoft 365, Excel 2021, Excel 2024, or a supported current version of Excel for Mac or the web.

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.