How to Find XLOOKUP in Excel and Use It in a Formula

To find XLOOKUP in Excel for Microsoft 365 on Windows, open the Formulas tab and choose Lookup & Reference, then select XLOOKUP. You can also click a cell and type =XLOOKUP( to start a formula. XLOOKUP searches one range for a value and returns a related result from another range; you need a version of Excel that includes the function and a workbook with lookup data.

What You’ll Need

Excel 2021, Excel 2024, or Microsoft 365. XLOOKUP is available in these versions. It is also available in Excel for the web and current Excel for Mac. It is not built into Excel 2019 or Excel 2016, so a formula using it may not calculate there.
A lookup value and two ranges. One range contains the values Excel should search, and another contains the matching results to return. Keep the ranges the same height or width.
A saved copy of important data. You are only entering a formula, but a copy makes it easier to recover if you accidentally change source data while preparing the example.

Step-by-Step

Check that your Excel version supports XLOOKUP. In Excel for Microsoft 365 on Windows, select File > Account and check the product information. Excel 2021 and Excel 2024 also include XLOOKUP. In Excel for the web, the function is available when you open a workbook in a supported account. If you are using Excel 2019 or 2016, updating may be necessary; otherwise, consider a compatible alternative such as VLOOKUP or INDEX and MATCH. The precise update options depend on your license.
Locate the function through the ribbon, if you want to browse for it. Select an empty cell, open the Formulas tab, and open Lookup & Reference in the Function Library group. Choose XLOOKUP. Excel opens the Function Arguments dialog, where you can fill in the search value, search range, and result range. Ribbon placement can vary slightly with window size or a customized ribbon, but the function is in the Lookup & Reference category in supported desktop versions.
Or start the formula by typing its name. Select the cell where the answer should appear and type =XLOOKUP(. If Excel displays the function suggestion, select it or press Tab to accept it. The opening parenthesis tells Excel you are entering the function’s arguments. If no suggestion appears, finish typing the function name and continue; autocomplete is a convenience, not a requirement. You can also click the Insert Function button marked fx beside the formula bar and search for XLOOKUP.
Identify the three essential inputs before completing the formula. The first input is what you want Excel to find. The second is the single row or column to search. The third is the row or column containing the answer. For a simple exact search, these three inputs are enough; the optional arguments let you customize what happens when there is no match or use a different match rule.
Enter a small example and check the returned value. Suppose product IDs are in A2:A5, product names are in B2:B5, and E2 contains the ID P-103. In this layout, the formula below searches for E2 in A2:A5 and returns the item from the corresponding position in B2:B5; the expected result is Desk lamp.
=XLOOKUP(E2,A2:A5,B2:B5,"Not found")
The fourth argument is optional and supplies the message Excel should show if there is no match. The default match is exact, so you do not need to add a separate exact-match setting for ordinary ID lookups.
Use the Function Arguments dialog if you prefer prompts. After choosing XLOOKUP from the ribbon or Insert Function, enter E2 for Lookup_value, A2:A5 for Lookup_array, and B2:B5 for Return_array. You may enter Not found in If_not_found. Leave the match and search options blank for a standard exact search. Select Done or OK to insert the formula, depending on the Excel interface. Check the formula bar to see the formula Excel created.
Test a value that is not in the list. Keep product IDs in A2:A5, names in B2:B5, and put P-999 in E2. Using the same formula layout, this formula returns Not found rather than an error because the fourth argument specifies that response:
=XLOOKUP(E2,A2:A5,B2:B5,"Not found")
This is a useful check before relying on the lookup: try both an ID you know is present and one you know is absent. If the result is unexpected, verify the cell contents and ranges before changing the match options.
Reuse the formula carefully. If you copy it to other rows, Excel may adjust the range references. For a formula that should always search the same source list, use absolute references for the lookup and return ranges; select a reference in the formula bar and press F4 on Windows to cycle through reference styles. On a Mac, the shortcut may be Fn+F4 depending on keyboard settings; you can also type dollar signs into the references. Keep the cell containing the search value relative if each copied row should look up a different value.

Common Mistakes

Confusing the function with Find. Ctrl+F searches visible worksheet content; it does not locate or insert a function. On Windows, use the Formulas tab, Insert Function, or type the function name into a cell. On a Mac, Command+F searches worksheet content, while the formula and ribbon methods are similar.

Using a version without XLOOKUP. Excel 2016 and Excel 2019 do not include the function. If a workbook opened in an older release shows #NAME? or a function name prefixed with _xlfn., the installed Excel may not recognize it. Open the file in a supported version or replace the formula with a function compatible with that version.

Choosing mismatched ranges. A vertical lookup range should normally pair with a vertical return range of the same number of rows. If the search range has four cells but the return range has five, the correspondence is unclear and Excel can return an error. Select the ranges deliberately, checking that the first search item lines up with the first result item.

Expecting a partial match by default. XLOOKUP uses exact matching by default. Searching for a short part of a longer text value will not automatically find it. If you specifically need wildcard matching, XLOOKUP supports wildcards when its match mode is set accordingly; use that option only when the data and intended match are clear.

Overwriting the source range. Put the formula outside the cells it searches or returns from. Placing a formula inside its own source data can lead to circular references or interfere with the list you are trying to look up.

Tips

Give the lookup and return columns clear headers, and check that IDs use a consistent format. A number stored as text may not match the same-looking numeric value. Extra spaces in names or codes can also prevent an exact match, so clean the source values if the displayed text appears identical but XLOOKUP reports no result.

Use the optional not-found argument to show a helpful message instead of #N/A. Keep the message specific, such as “ID not listed,” so readers can tell a missing record from a broken formula. Do not use a blank response unless hiding a missing match is genuinely useful; a blank can make incomplete data harder to notice.

XLOOKUP can return values from either side of the search column, unlike VLOOKUP’s usual left-to-right setup. It can also return a row or column of results when the return range contains multiple columns or rows, with supported Excel versions spilling results into neighboring empty cells. Make sure the spill area is clear before relying on that behavior.

For repeated lookups in a formatted Excel table, structured references can make formulas easier to read and maintain as rows are added. You can also use Formulas > Evaluate Formula to inspect how Excel processes a complex formula step by step. For a first lookup, though, ordinary cell references are usually easier to understand and troubleshoot.

Troubleshooting

XLOOKUP is missing from the ribbon or autocomplete. Confirm your Excel version and update channel under File > Account. If you are in Excel 2016 or 2019, the function is not included. If you are in a supported version, type the complete function name in a cell or search for it with the Insert Function button. A customized ribbon or narrow window can hide groups, so expanding the window or using the search box may help locate commands.

The formula returns #N/A. That usually means Excel did not find an exact match. Check for leading or trailing spaces, different number-versus-text formats, spelling differences, or a lookup value that is not present. Confirm that the lookup range includes the expected item. If you want a readable response instead of #N/A, provide a not-found value as the fourth argument, as in the example.

The formula returns the wrong row’s result. Check whether the search and return ranges start on corresponding rows and cover the intended data. Look for duplicate IDs: XLOOKUP returns the first matching item it encounters by default. If duplicates exist, decide which one is appropriate or make the lookup key unique. Also verify that a copied formula has not shifted its source ranges.

Excel shows an error as soon as you enter the formula. Check that the formula starts with an equals sign, that each opening parenthesis has a closing parenthesis, and that text values are enclosed in straight double quotes. In some regional settings Excel uses semicolons instead of commas as separators; use the separator Excel expects on your computer. If you see #SPILL! from a multi-column or multi-row return, clear the cells blocking the output.

Frequently Asked Questions

Where is XLOOKUP in Excel?
In supported desktop versions, open Formulas > Lookup & Reference and select XLOOKUP. You can also type =XLOOKUP( in a cell or find it with the Insert Function button.

Why can’t I find XLOOKUP in Excel 2019?
Excel 2019 does not include XLOOKUP, and installing ordinary updates does not add it to that version. Use a supported version such as Microsoft 365, Excel 2021, or Excel 2024, or choose an older compatible lookup method.

Do I need to enter all six XLOOKUP arguments?
No. A basic lookup needs the value to find, the range to search, and the range to return from. The not-found, match-mode, and search-mode arguments are optional.

Does XLOOKUP search left?
Yes. The lookup range and return range are chosen separately, so the result can be to the left or right of the search range. Their positions do not have to follow VLOOKUP’s left-to-right arrangement.

Can XLOOKUP return more than one result column?
Yes. In supported Excel versions, a multi-column return range can spill its results into adjacent cells. Ensure those cells are empty, and confirm that the selected lookup row matches the row layout of the return range.

Is XLOOKUP available in Excel for Mac and the web?
Yes, it is available in current Microsoft 365 versions for Mac and Excel for the web. Ribbon layout and keyboard shortcuts differ somewhat from Windows, but the function name and basic formula structure are the same.

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.