Using XLOOKUP to find a specific value

XLOOKUP is a powerful new function in Excel that allows you to search a range of cells for a specific value and return a corresponding value from another range of cells. It’s similar to the VLOOKUP function, but with several advantages, including the ability to search for values in any column and to return multiple values.

In this tutorial, I will walk you through the steps of using the XLOOKUP function in Excel.

Step 1: Understanding the syntax

The syntax for XLOOKUP is as follows:

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

• Lookup_value: The value you want to find in the lookup_array.
• Lookup_array: The range of cells to search for the lookup_value.
• Return_array: The range of cells containing the values you want to return.
• If_not_found (optional): The value to return if the lookup_value is not found. By default, it returns #N/A.
• Match_mode (optional): Specifies how Excel should match the lookup_value with values in the lookup_array. It can be “0” for exact match, “1” for exact match or next smallest value, “-1” for exact match or next largest value, or “2” for wildcard match.
• Search_mode (optional): Specifies whether to search from the beginning or end of the lookup_array. It can be “1” for search from beginning or “(-1)” for search from end.

Step 2: Setting up the data

To demonstrate the XLOOKUP function, we will create a simple table with student names, IDs, and grades. The table will look like this:

Step 3: Using XLOOKUP to find a specific value

Suppose we want to find the grade of student with ID 003. We can use the XLOOKUP function to do this. Here’s how:

1. Select cell D2 (or any cell where you want the result to be displayed).
2. Type the following formula:

=XLOOKUP(“003”,B2:B6,C2:C6)

3. Press Enter.

The result will be 78, which is the grade for the student with ID 003.

Step 4: Using XLOOKUP with if_not_found

If the lookup_value is not found in the lookup_array, XLOOKUP will return #N/A by default. However, you can specify a different value to return using the if_not_found parameter.

Let’s say we want to find the grade of a student with ID 006, which does not exist in the table. We can use the XLOOKUP function with if_not_found to return a custom message instead of #N/A. Here’s how:

1. Select cell D3 (or any cell where you want the result to be displayed).
2. Type the following formula:

=XLOOKUP(“006″,B2:B6,C2:C6,”Student not found”)

3. Press Enter.

The result will be “Student not found”, which is the value we specified for if_not_found.

Step 5: Using XLOOKUP with match_mode

The match_mode parameter allows you to specify how Excel should match the lookup_value with values in the lookup_array. There are four options:

• “0”: Exact match (default).
• “1”: Exact match or next smallest value.

• “-1”: Exact match or next largest value.
• “2”: Wildcard match.

Let’s say we want to find the grade of the student with the closest ID to 003. We can use the XLOOKUP function with match_mode to find the next smallest value. Here’s how:

1. Select cell D4 (or any cell where you want the result to be displayed).
2. Type the following formula:

=XLOOKUP(“003”,B2:B6,C2:C6,0,1)

3. Press Enter.

The result will be 85, which is the grade for the student with ID 001, the next smallest value.

Step 6: Using XLOOKUP with search_mode

The search_mode parameter allows you to specify whether to search from the beginning or end of the lookup_array. There are two options:

• “1”: Search from beginning (default).
• “-1”: Search from end.

Let’s say we want to find the ID of the student with the last name “Smith”, but we’re not sure if it’s at the beginning or end of the lookup_array. We can use the XLOOKUP function with search_mode to search from the end. Here’s how:

1. Select cell D5 (or any cell where you want the result to be displayed).
2. Type the following formula:

=XLOOKUP(“Smith”,A2:A6,B2:B6,0,-1)

3. Press Enter.

The result will be 004, which is the ID for the student with the last name “Smith” at the end of the lookup_array.

Conclusion:

In this tutorial, we learned how to use the XLOOKUP function in Excel to search for values in a range of cells and return corresponding values from another range of cells. We also explored the different parameters of XLOOKUP, including if_not_found, match_mode, and search_mode, and how to use them to customize the function’s behavior. XLOOKUP is a versatile and powerful function that can save you time and effort when working with large datasets in Excel.