The search_mode parameter in the XLOOKUP function in Microsoft Excel specifies the direction or method Excel should use when searching for the lookup_value.
This parameter can greatly affect how the function operates, especially in large datasets or specific search scenarios.
Here’s a detailed explanation of each option within the search_mode parameter, along with examples.
Table of Contents
Introduction XLOOKUP function
The XLOOKUP function in Microsoft Excel is a versatile and powerful tool introduced to replace older functions like VLOOKUP, HLOOKUP, and LOOKUP.
It is used to search for a specified value within an array or range and return a corresponding value from another array or range.
Here is the basic syntax of the XLOOKUP function:
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])- lookup_value: The value you are searching for.
- lookup_array: The array or range containing the value to look up.
- return_array: The array or range from which to return a value.
- if_not_found (optional): The value to return if the lookup_value is not found.
- match_mode (optional): Specifies whether to look for an exact match or an approximate match.
- search_mode (optional): Specifies the search mode, such as binary search or reverse search.
search_mode Options in XLOOKUP function
- 1 (Default): Search starts at the first item and moves towards the end. It’s a straightforward search.
- -1: Search starts at the last item and moves towards the first. This is useful when you expect the value to be closer to the end.
- 2: A binary search that requires the lookup array to be sorted in ascending order. This is faster for large sorted datasets.
- -2: A binary search that requires the lookup array to be sorted in descending order. Like 2, but for datasets sorted in reverse.
Example 1: Default Search (search_mode = 1)
Scenario:
You have a list of fruits in column A (A2:A10) and their prices in column B (B2:B10). You want to find the price of “Pear.”
Formula:
= XLOOKUP("Pear", A2:A10, B2:B10, "Not Found", 0, 1)Explanation:
This formula will start searching for “Pear” at A2 and move down each row until it finds “Pear” or reaches the end of the range. If “Pear” is found, it returns the corresponding price from column B. If not found, it returns “Not Found.”
Example 2: Reverse Search (search_mode = -1)
Scenario:
Suppose “Pear” is a seasonal fruit and often appears towards the bottom of your updated list. The list is in column A (A2:A10), and their quantities are in column B (B2:B10).
Formula:
= XLOOKUP("Pear", A2:A10, B2:B10, "Not Found", 0, -1)Explanation:
Here, Excel starts the search from A10 and moves upwards, which is efficient if “Pear” is more likely to be near the end of the list. It returns the corresponding quantity from column B or “Not Found” if “Pear” is not in the list.
Example 3: Binary Search in Ascending Order (search_mode = 2)
Scenario:
You have a list of product IDs in ascending order in column A (A2:A10) and their corresponding stock counts in column B (B2:B10). You need to check the stock for product ID 500.
Formula:
= XLOOKUP(500, A2:A10, B2:B10, "Not Found", 0, 2)Explanation:
This formula performs a binary search, which is efficient for large, sorted data. It quickly narrows down the search area by comparing the midpoint of the range, drastically reducing the number of comparisons needed to find product ID 500. It returns the stock count for product ID 500 or “Not Found” if it isn’t in the list.
Example 4: Binary Search in Descending Order (search_mode = -2)
Scenario:
Your dataset is sorted in descending order, with customer IDs in column A (A2:A10) and their corresponding loyalty points in column B (B2:B10). You want to find the loyalty points for customer ID 500.
Formula:
= XLOOKUP(500, A2:A10, B2:B10, "Not Found", 0, -2)Explanation:
Similar to the previous example, but now the data is sorted in descending order. The binary search here quickly finds customer ID 500 in the sorted list and returns their loyalty points or “Not Found” if the customer ID isn’t present.
Conclusion
- The binary search options (2 and -2) are much faster than the regular search, but they require the data to be pre-sorted in the specified order.
- If the data isn’t sorted as required for the binary search modes, the results could be unpredictable.
- The choice of search_mode can be vital for optimizing performance in large datasets.
Using the appropriate search_mode can make your XLOOKUP function more efficient and tailored to the specific layout and requirements of your data.
Return All Matches in Excel Using XLOOKUP and Other Functions
2 ways to use XLOOKUP with Multiple Criteria in Excel
Understanding Match Mode in Excel XLOOKUP Function
Top 8 Powerful Excel XLOOKUP Examples: Enhance Your Data Analysis Skills