How to Use the XLOOKUP Function in Excel
How to Use the XLOOKUP Function in Excel GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office
lookup_value: The value you want to search forlookup_array: The array (column) you want to searchreturn_array: The result (column) you want to retrieve a value frommatch_mode (optional): Select an exact match (0), an exact match or next smallest value (-1), or a wildcard match (2).search_mode (optional): Select whether to search starting with the first item in the column (1), the last item in the column (-1), binary search ascending (2) or binary search descending (-2). The following are a few of the most common lookups you can do with the XLOOKUP function.
I2: Points to the Rep Name search cellC2:C44: This is the Rep column, which is the lookup arrayG2:G33: This is the Total column, which is the return array0: Selects an exact match1: Selects the first match in the results When you press Enter and type the name of a sales rep, the Total result cell will show you the first result in the table for that sales rep. If you want to search for the most recent sale (since the table is ordered by date in reverse order), change the last XLOOKUP argument to -1, which will start the search from the last cell in the lookup array and provide you with that result instead. This example shows a similar search that you could perform with a VLOOKUP function by using the Rep column as the first column of the lookup table. However, XLOOKUP lets you search for any column in either direction. For example, if you want to find the sales rep who sold the first Binder order of the year, you would use the folloing XLOOKUP function: =XLOOKUP(I2,D2:D44,C2:C44,0,1)
D2: Points to the Item search cellD2:D44: This is the Item column, which is the lookup arrayC2:C44: This is the Rep column, which is the return array to the left of the lookup array0: Selects an exact match1: Selects the first match in the results This time, the result will be the name of the sales rep who sold the first binder order of the year.
J2: Points to the Rep search cellB2:B42: This is the Item column, which is the column lookup arrayK2: Points to the Quarter search cellC1:H1: This is the row lookup arrayC2:H42: This is the lookup array for the dollar amount in each quarter This nested XLOOKUP function first identifies the sales rep, and the nexted XLOOKUP function identifies the desired quarter. The return value will is the cell where those two intercept. The result for this formula is the quarter one earnings for the representative with the name Thompson.
How to Use the XLOOKUP Function in Excel
With advanced search features that are better than VLOOKUP
By Ryan Dube Ryan Dube Writer University of Maine Ryan Dube is a freelance contributor to Lifewire and former Managing Editor of MakeUseOf, senior IT Analyst, and an automation engineer. lifewire's editorial guidelines Updated on January 18, 2020 Tweet Share Email Tweet Share EmailIn This Article
Expand Jump to a Section How XLOOKUP Works Search for a Single Result Vertical and Horizontal Match Using the XLOOKUP Function The VLOOKUP function has always been one of Excel's most powerful functions. It let you search for values in the first column of a table, and return values from fields on the right. But Excel also has a function called XLOOKUP, which allows you to search for a value in any column or row, and return data from any other column.How XLOOKUP Works
The XLOOKUP function is much easier to use than the VLOOKUP function, because instead of specifying a value for the results column, you can specify the entire range. The function also allows you to search both a column and a row, locating the value at the intersecting cell. The parameters of the XLOOKUP function are as follows: =XLOOKUP (lookup_value, lookup_array, return_array, [match_mode], [search_mode])lookup_value: The value you want to search forlookup_array: The array (column) you want to searchreturn_array: The result (column) you want to retrieve a value frommatch_mode (optional): Select an exact match (0), an exact match or next smallest value (-1), or a wildcard match (2).search_mode (optional): Select whether to search starting with the first item in the column (1), the last item in the column (-1), binary search ascending (2) or binary search descending (-2). The following are a few of the most common lookups you can do with the XLOOKUP function.
How to Search for a Single Result Using XLOOKUP
The easiest way to use XLOOKUP is to search for a single result using a data point from one column. This example spreadsheet is a list of orders submitted by sales representatives, including the item, number of units, cost, and total sale. If you want to find the first sale in the list submitted by a specific sales rep, you could create an XLOOKUP function that searches the Rep column for a name. The function will return the result from the Total column. The XLOOKUP function for this is: =XLOOKUP(I2,C2:C44,G2:G44,0,1)I2: Points to the Rep Name search cellC2:C44: This is the Rep column, which is the lookup arrayG2:G33: This is the Total column, which is the return array0: Selects an exact match1: Selects the first match in the results When you press Enter and type the name of a sales rep, the Total result cell will show you the first result in the table for that sales rep. If you want to search for the most recent sale (since the table is ordered by date in reverse order), change the last XLOOKUP argument to -1, which will start the search from the last cell in the lookup array and provide you with that result instead. This example shows a similar search that you could perform with a VLOOKUP function by using the Rep column as the first column of the lookup table. However, XLOOKUP lets you search for any column in either direction. For example, if you want to find the sales rep who sold the first Binder order of the year, you would use the folloing XLOOKUP function: =XLOOKUP(I2,D2:D44,C2:C44,0,1)
D2: Points to the Item search cellD2:D44: This is the Item column, which is the lookup arrayC2:C44: This is the Rep column, which is the return array to the left of the lookup array0: Selects an exact match1: Selects the first match in the results This time, the result will be the name of the sales rep who sold the first binder order of the year.
Perform Vertical and Horizontal Match with XLOOKUP
Another capability of XLOOKUP that VLOOKUP isn't capable of is the ability to perform both a vertical and horizontal search, meaning you can search for an item down a column, and across a row as well. This dual search feature is an effective replacement for other Excel functions like INDEX, MATCH, or HLOOKUP. In the following example spreadsheet, the sales for each sales rep are split by quarter. If you wanted to see the third quarter sales for a specific sales rep, without the XLOOKUP function, this kind of search would be difficult. With the XLOOKUP function, this kind of search is easy. Using the folloing nexted XLOOKUP function, you can search for the third quarter sales for a specific sales rep: =XLOOKUP(J2,B2:B42,XLOOKUP(K2,C1:H1,C2:H42))J2: Points to the Rep search cellB2:B42: This is the Item column, which is the column lookup arrayK2: Points to the Quarter search cellC1:H1: This is the row lookup arrayC2:H42: This is the lookup array for the dollar amount in each quarter This nested XLOOKUP function first identifies the sales rep, and the nexted XLOOKUP function identifies the desired quarter. The return value will is the cell where those two intercept. The result for this formula is the quarter one earnings for the representative with the name Thompson.