How to Use XLOOKUP Function in Excel?

Introduction to Excel XLOOKUP Function

Summary:

Searches a range or an array for a match and returns the corresponding item from a second range or array.

Syntax:

=XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Arguments:

Argument	Required/Optional	Explanation
lookup_value	Required	It’s the specified value that needs to be searched for in the data table.
lookup_array	Required	The lookup_array is a range of cells or an array where the lookup_value will be searched for.
return_array	Required	It’s the second range of cells or an array from where the output data will be extracted.
if_not_found	Optional	If the lookup value is not found, a customized message can be inserted using this argument in text format.
match_mode	Optional	It defines if the function will look for an exact match based on specified criteria or a wildcard character match.
search_mode	Optional	It denotes the search order (in ascending or descending order, from last to first or first to last).

Return Parameter:

The XLOOKUP function allows you to look for a value in a dataset and return the corresponding value in some other row/column.

How to Use XLOOKUP Function in Excel: 11 Practical Examples

Example 1 – Use the XLOOKUP Function for Exact Match

We will provide the movie name and derive the genre using the XLOOKUP function.

Steps:

Select Cell G5 and insert the following formula.

=XLOOKUP(F5,B5:B9,D5:D9)

Press Enter to get the lookup value.

We have used Cell Reference in our formula. Cell F5 contains the lookup_value, and cell ranges B5:B9 and D5:D9 are the lookup_array and the return_array respectively. We have not set any value for match_type as, by default, it checks Exact Match.

Example 2 – Find an Approximate Match Using the Excel XLOOKUP Function

We will find the discount for the given price.

Steps:

Enter the following formula in Cell G5.

=XLOOKUP(F5,C5:C9,D5:D9,,-1)

Press Enter to find the corresponding value of Discount.

We inserted -1 as the match_type field. Our inserted lookup_value was $550 which was not on our list. Since we used -1, this gave the nearest smaller value. So we have found a discount of $500.

The approximate matches can be done by another approach.
Enter the following formula in Cell G5.

=XLOOKUP(F5,C5:C9,D5:D9,,1)

Press Enter.

We inserted 1 as our match_type field. That’s why this returned the nearest larger value.

Example 3 – Return Multiple Values by Applying the XLOOKUP Function in Excel

We will find all the listed details for a given movie name.

Steps:

Select Cell C12 and insert the following formula.

=XLOOKUP(B12,B5:B9,C5:E9)

Press Enter to get all the details available of that movie.

We inserted Cell B12 as lookup_value, and cell ranges B5:B9 and C5:E9 are the lookup_array and the return_array respectively.

Example 4 – Apply the Excel XLOOKUP Function for Multiple Criteria

We have selected a sample dataset of the roll of honor of 5 European leagues. Now, we will set the name of the league and the status as criteria and look for the corresponding Team.

Steps:

Enter the following formula in Cell G7.

=XLOOKUP(G6,C4:D4,XLOOKUP(G5,B5:B9,C5:D9))

Press Enter.

How Does the Formula Work?

We have used two XLOOKUP. Within the first XLOOKUP, we checked the status of the team.
We have set the second XLOOKUP at the return_array
Within this second XLOOKUP function, we have checked the league name and set the team names as the return_array.
We have found the name of the team.

Example 5 – Execute Horizontal Lookup Using XLOOKUP Function

The sample dataset below contains several players with their goals and assists. We will find the goals and assists for a given player. The name of the player will be our lookup_value and stored in Cell C9.

Steps:

Enter the following formula in Cell C10.

=XLOOKUP(C9,C4:F4,C5:F6)

Press Enter.

In the XLOOKUP function, we inserted Cell C9 as lookup_value, cell ranges C4:F4 and C5:F6 as lookup_array and return_array respectively.

Example 6 – Use Nested XLOOKUP Formula in Excel

From the scorer dataset, we want to find the goal involvement (sum of goals and assists) for a given player.

Steps:

Select Cell C9 and insert the following formula.

=SUM(XLOOKUP(C8,C4:F4,C5:F5),XLOOKUP(C8,C4:F4,C6:F6))

Press Enter.

How Does the Formula Work?

The two XLOOKUP functions will provide goals and assists respectively for a player.
The SUM function will add them together and provide the result.

Example 7 – Solve #N/A Error Using XLOOKUP Function in Excel

From our movie dataset if we set a value that is within our table and find the value. If we set a movie name that is not on the table, then the #N/A error occurs.

We provided Godzilla as the movie name and the error occurred.

To remove the error, follow the steps given below.

Steps:

Enter the following formula in Cell G5.

=XLOOKUP(F5,B5:B9,D5:D9,"Not Found")

Press Enter to execute the formula.

In the formula, we have set “Not Found” as the if_not_found value. Since our provided movie was not in the dataset, we have found the message “Not Found”.

Example 8 – Utilize XLOOKUP with Different Functions for Complex Criteria

We have a sample dataset containing some bank Account No, their corresponding Country, the amount of cash and the Date of transaction of those accounts.

Search the data for the account that begins with “x” and Country is “Australia” and the month is not March.

Steps:

Select Cell B13 and insert the following formula.

=XLOOKUP(1,(LEFT(B5:B10)="x")*(C5:C10="Australia")*NOT(MONTH(D5:D10)=3),B5:E10)

Press Enter.

How Does the Formula Work?

The LEFT function will check if there is “x” on the left of the given cell range B5:B10.
The MONTH function will find out the month value of the cell range D5:D10.
The NOT function will check if the resultant of the MONTH is not equal to 3.
Using the results of these functions, the XLOOKUP will return the desired data range.