---
title: "XLOOKUP function | Microsoft Support"
url: https://stacklist.com/card/ec7348a6-2388-4dba-80af-8b19e6ffeba5
source_url: "https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929"
stack: https://stacklist.com/c/business/stack/c63a8f97-5e08-4630-8e71-f2794bad0e8b
summary: "XLOOKUP function searches a range or array and returns the corresponding item from a match, with support for approximate matches and multiple search modes. It offers advantages over VLOOKUP by allowing searches in any direction and returning multiple columns from a single formula."
tags: "xlookup, excel, lookup-function, spreadsheet, formula, data-retrieval"
key_entities: "XLOOKUP (technology), Excel (technology), VLOOKUP (technology), Microsoft (organization), lookup_value (concept), match_mode (concept)"
classification: "reference"
content_hash: "sha256:481a7665c63acfcff37d2f9df922f3ea9bba0a8cc3e3ed286362032fa3b9367e"
acp_version: "0.2"
token_counts_approximate: 1311
visibility: public
agent_accessible: true
status: "final"
---

# XLOOKUP function | Microsoft Support

XLOOKUP function Applies To Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016 Excel for iPad Excel for iPhone Excel for Android tablets Excel for Android phones Get help with Copilot Explain how XLOOKUP works How can I format a sparkline chart? Write an XLOOKUP formula I can paste and adapt Use the XLOOKUP function to find things in a table or range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term and return a result from the same row in another column, regardless of which side the return column is on. Note XLOOKUP is not available in Excel 2016 and Excel 2019. However, you may come across a situation of using a workbook in Excel 2016 or Excel 2019 with the XLOOKUP function in it, if it was created by someone else using a newer version of Excel. Syntax The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match. =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) Argument Description lookup_value Required* The value to search for *If omitted, XLOOKUP returns blank cells it finds in lookup_array . lookup_array Required The array or range to search return_array Required The array or range to return [if_not_found] Optional Where a valid match is not found, return the [if_not_found] text you supply. If a valid match is not found, and [if_not_found] is missing, #N/A is returned. [match_mode] Optional Specify the match type: 0 - Exact match. If none found, return #N/A. This is the default. -1 - Exact match. If none found, return the next smaller item. 1 - Exact match. If none found, return the next larger item. 2 - A wildcard match where *, ?, and ~ have special meaning . [search_mode] Optional Specify the search mode to use: 1 - Perform a search starting at the first item. This is the default. -1 - Perform a reverse search starting at the last item. 2 - Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned. -2 - Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned. Examples Example 1 uses XLOOKUP to look up a country name in a range, and then return its telephone country code. It includes the lookup_value (cell F2), lookup_array (range B2:B11), and return_array (range D2:D11) arguments. It doesn't include the match_mode argument, as XLOOKUP produces an exact match by default. Note XLOOKUP uses a lookup array and a return array, whereas VLOOKUP uses a single table array followed by a column index number. The equivalent VLOOKUP formula in this case would be: =VLOOKUP(F2,B2:D11,3,FALSE) ——————————————————————————— Example 2 looks up employee information based on an employee ID number. Unlike VLOOKUP, XLOOKUP can return an array with multiple items, so a single formula can return both employee name and department from cells C5:D14. ——————————————————————————— Example 3 adds an if_not_found argument to the preceding example. ——————————————————————————— Example 4 looks in column C for the personal income entered in cell E2, and finds a matching tax rate in column B. It sets the if_not_found argument to return 0 (zero) if nothing is found. The match_mode argument is set to 1 , which means the function will look for an exact match, and if it can't find one, it returns the next larger item. Finally, the search_mode argument is set to 1 , which means the function will search from the first item to the last. Note XARRAY's lookup_array column is to the right of the return_array column, whereas VLOOKUP can only look from left-to-right. ——————————————————————————— Example 5 uses a nested XLOOKUP function to perform both a vertical and horizontal match. It first looks for Gross Profit in column B, then looks for Qtr1 in the top row of the table (range C5:F5), and finally returns the value at the intersection of the two. This is similar to using the INDEX and MATCH functions together. Tip You can also use XLOOKUP to replace the HLOOKUP function. Note The formula in cells D3:F3 is: =XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17)) . ——————————————————————————— Example 6 uses the SUM function , and two nested XLOOKUP functions, to sum all the values between two ranges. In this case, we want to sum the values for grapes, bananas, and include pears, which are between the two. The formula in cell E3 is: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10)) How does it work? XLOOKUP returns a range, so when it calculates, the formula ends up looking like this: =SUM($E$7:$E$9) . You can see how this works on your own by selecting a cell with an XLOOKUP formula similar to this one, then select Formulas > Formula Auditing > Evaluate Formula , and then select Evaluate to step through the calculation. Note Thanks to Microsoft Excel MVP, Bill Jelen , for suggesting this example. ———————————————————————————