How to use XLOOKUP function
XLOOKUP function is a powerful lookup function in Microsoft Excel that allows you to search and retrieve data from a range of cells. It was introduced in Microsoft Office 365 as a replacement for the traditional VLOOKUP and HLOOKUP functions, offering improved functionality and flexibility. XLOOKUP offers various options to retrieve data based on different conditions, making it a more efficient and versatile tool for data retrieval.
In this article, we will go through how to use the XLOOKUP function and its parameters in detail, including examples to illustrate how it can be used in various situations.
Syntax of XLOOKUP Function: The syntax of the XLOOKUP function is as follows:
XLOOKUP (lookup_value, lookup_array, [return_array], [match_mode], [search_mode])Parameters of XLOOKUP Function:
- lookup_value: The value that you want to search for in the lookup_array.
- lookup_array: The range of cells that you want to search for the lookup_value.
- return_array: The range of cells that you want to return the corresponding values from. If this parameter is omitted, XLOOKUP will return the value in the same column as the lookup_value in the lookup_array.
- match_mode: The type of match you want to perform. The options are -1, 0, and 1. -1 performs a exact match, 0 performs an approximate match, and 1 performs an exact or next smaller match. The default value is 1.
- search_mode: The direction of the search. The options are -1, 0, and 1. -1 searches from the last to the first, 0 searches the entire range, and 1 searches from the first to the last. The default value is 1.
Examples of XLOOKUP Function:
1. Exact Match: To perform an exact match, use the match_mode parameter as -1. For example, if you want to find the salary of a particular employee from a table, you can use the following formula:
=XLOOKUP(A1,B1:D10,E1:E10,-1)Where A1 contains the name of the employee, B1:D10 is the range of cells containing the employee names, and E1:E10 is the range of cells containing their salaries.
2. Approximate Match: To perform an approximate match, use the match_mode parameter as 0. For example, if you have a table of grades and you want to find the corresponding grades based on a given score, you can use the following formula:
=XLOOKUP(A1,B1:B5,C1:C5,0)Where A1 contains the score, B1:B5 is the range of cells containing the lower limit of each grade, and C1:C5 is the range of cells containing the corresponding grades.
3. Next Smallest Match: To perform an exact or next smallest match, use the match_mode parameter as 1. For example, if you have a table of product prices and you want to find the price of a particular product, you can use the following formula:
=XLOOKUP(A1,B1:B5,C1:C5,1)Where A1 contains the product name, B1:B5 is the range of cells containing the product names, and C1:C5 is the range of cells containing their prices.
