The XLOOKUP function in Excel is a powerful tool that allows you to search for a specific value in a range and retrieve a corresponding value from another range. It works in a similar way to the VLOOKUP and HLOOKUP functions but provides more flexibility and functionality.

Syntax

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

Parameters

1. lookup_value: The value you want to find in the lookup_array.

2. lookup_array: The range of cells that contains the values to be searched.

3. return_array: The range of cells containing the corresponding values to be returned.

4. if_not_found (optional): The value or action to be performed if the lookup_value is not found. This parameter can be omitted.

5. match_mode (optional): Determines the type of match to be performed. This parameter can be omitted, and the default value is 1 (exact match).

6. search_mode (optional): Determines the direction of the search. This parameter can be omitted, and the default value is 1 (search from top to bottom).

Examples

Let's take a look at a few examples to understand how the XLOOKUP function works:

Example 1:

=XLOOKUP(42, A1:A10, B1:B10)

In this example, we're searching for the value 42 in the range A1:A10 and returning the corresponding value from the range B1:B10.

Example 2:

=XLOOKUP("apple", A1:A10, B1:B10, "Not found")

In this example, we're searching for the value "apple" in the range A1:A10. If the value is found, we'll return the corresponding value from the range B1:B10. Otherwise, we'll display the text "Not found".

Example 3:

=XLOOKUP(D2, D2:D10, B2:B10, 0, -1)

This example demonstrates how to perform a reverse search. We're searching for the value in cell D2 within the range D2:D10, and we'll return the corresponding value from the range B2:B10. The match_mode is set to 0 (exact match), and the search_mode is set to -1 (search from bottom to top).