You need Excel to find a value in one range and return the matching result from another range. XLOOKUP handles that with a cleaner formula than older lookup setups, and exact match is already built in. Start with the basic formula, then add the options that match the lookup you actually need.
1. Build The Basic XLOOKUP Formula
Use this when you want Excel to search one column or row and return a matching value from another column or row.
- 1.Select the blank cell where the answer should appear.
- 2.Type
=XLOOKUP(. - 3.Enter the value to find, the range to search, and the range that contains the result.
- 4.Close the formula with
). - 5.Press Enter.
The basic pattern is =XLOOKUP(lookup_value, lookup_array, return_array). For example, =XLOOKUP(F2,B2:B11,D2:D11) searches B2:B11 for the value in F2, then returns the matching value from D2:D11.
2. Trust Exact Match By Default
- For product IDs, names, order numbers, employee IDs, and email addresses, keep the formula simple.
- XLOOKUP uses exact match when you leave out the optional match mode.
- Enter
=XLOOKUP(lookup_value, lookup_array, return_array), then press Enter. Excel looks for the exact value and returns the matching result.
3. Replace The #N/A Error With A Message
A missing lookup value returns #N/A; the fourth argument lets you show your own message instead.
- 1.Select the result cell.
- 2.Enter
=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found). - 3.Replace
if_not_foundwith text in quotation marks, such as"Employee not found". - 4.Press Enter.
For example, =XLOOKUP(B2,B5:B14,C5:D14,"Employee not found") returns matching data from C5:D14 or shows Employee not found.
4. Return Multiple Columns At Once
XLOOKUP also returns more than one column or row from a single formula. Select the first cell where the spilled result should begin, then use a multi-column or multi-row return range.
Use a formula such as =XLOOKUP(B2,B5:B14,C5:D14), then press Enter. Excel returns the matching values from the full return range, starting in the cell where you entered the formula.
5. Match Wildcards Or Nearby Values
- Use wildcard matching when the lookup value contains partial text. Enter
=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, 2), then use*,?, or~in the lookup value. - Use approximate match when the closest lower or higher value is the right answer. Enter
=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, -1)for an exact match or the next smaller item. - Enter
=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, 1)for an exact match or the next larger item.
6. Search From The End Or Speed Through Sorted Data
Reverse search starts with the last item first. Add reverse search mode as the sixth argument: =XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, -1). Excel starts at the last item instead of the first.
For binary search, sort the lookup range first. Sort ascending and use search_mode 2, or sort descending and use search_mode -2. Keep the lookup range sorted exactly that way, because Microsoft warns that unsorted data returns invalid results.
7. Insert XLOOKUP With Excel Helpers
- 1.Formula AutoComplete inserts the function while you type. Select a cell, type
=and the first letters ofXLOOKUP, choose XLOOKUP from the drop-down list with Tab or by double-clicking it, complete the arguments, then press Enter. - 2.The Function Arguments wizard gives you labeled fields instead. Select a cell, select the Insert Function fx button on the formula bar or press Shift+F3, search for XLOOKUP, double-click XLOOKUP, fill the argument fields, then select OK.
To turn formula suggestions on or off in Windows, open File > Options > Formulas, then select or clear Formula AutoComplete. On Mac, open Excel > Preferences > AutoComplete, then select or clear Show AutoComplete options for functions and named ranges.
8. Use A Supported Excel Version
XLOOKUP works in Excel for Microsoft 365, Excel 2024, Excel 2021, Excel for Microsoft 365 for Mac, Excel 2024 for Mac, Excel 2021 for Mac, Excel for iPad, Excel for iPhone, and Excel for Android phones and tablets. It also works in Excel for the web through Microsoft 365 on the web.
- Excel 2016 and Excel 2019 do not support XLOOKUP.
- When those versions open a workbook created in a newer Excel version, the formula shows
_xlfn.before the function or returns#NAME?. - Open the workbook in a supported Excel version.
To install or reinstall a supported desktop app, go to m365.cloud.microsoft/apps, sign in, and choose Install apps. On Microsoft’s Excel product page, choose Download Excel for Windows, Download Excel for Android, Download Excel for iOS, Download Excel for MacOS, or Open Excel under Web Portal.
9. Ask Copilot To Draft The Formula
Eligible Microsoft 365 Copilot users can use Copilot in Excel to create an XLOOKUP-based lookup column. Open the workbook, select the Copilot icon in the lower-right corner of Excel, and prompt Copilot to create a column that looks up matching values from another table or sheet.
Review the plan or generated result, then accept it or edit the XLOOKUP formula before keeping it.
To manage Copilot formula suggestions in Windows, open File > Options > Copilot, select or clear the formula suggestion options, then select OK. In Excel for the web, open File > Options > Copilot Settings, clear the relevant check boxes, then select OK.
Frequently Asked Questions
Why is XLOOKUP showing #NAME? in Excel?
XLOOKUP is not available in Excel 2016 or Excel 2019. Open the workbook in Excel for Microsoft 365, Excel 2024, Excel 2021, a supported Mac or mobile version, or Excel for the web.
Do I need to choose exact match in XLOOKUP?
No. Exact match is the default, so =XLOOKUP(lookup_value, lookup_array, return_array) already looks for an exact match.
Can XLOOKUP return more than one column?
Yes. Use a multi-column return_array, such as C5:D14, and Excel returns the matching values from that range as a spilled result.
Can I use XLOOKUP in Excel for the web?
Yes. Open Excel on the web, select a cell, type =XLOOKUP(, complete the arguments, and press Enter.
