In this tutorial, we will walk you through how to use XLOOKUP with two criteria, with step-by-step instructions and examples.
Example Table: Let’s use the following example table to illustrate how to use XLOOKUP with two criteria. We want to find the sales for a specific salesperson in a specific month.
| Salesperson | Month | Sales |
|---|---|---|
| John | Jan | 200 |
| Mary | Jan | 300 |
| John | Feb | 250 |
| Mary | Feb | 350 |
| John | Mar | 150 |
| Mary | Mar | 400 |
Step 1: Set up the lookup values First, you need to define the values you are looking up. In this case, we want to find the sales for John in February. You can enter these values into two separate cells in your worksheet.
For example, you can enter “John” into cell A8 and “Feb” into cell B8.
Step 2: Set up the XLOOKUP formula Next, you need to set up the XLOOKUP formula. The basic syntax of the XLOOKUP function is as follows:
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
In this case, we want to use two criteria to search for the sales. We will use the following formula:
=XLOOKUP(lookup_value1 & lookup_value2, lookup_array1 & lookup_array2, return_array)
Explanation of the formula:
- lookup_value1: The first lookup value (in our case, the salesperson’s name “John”)
- lookup_value2: The second lookup value (in our case, the month “Feb”)
- lookup_array1: The first lookup array (in our example table, the Salesperson column)
- lookup_array2: The second lookup array (in our example table, the Month column)
- return_array: The range of cells from which to return a value (in our example table, the Sales column)
For our example, the formula would be:
=XLOOKUP(A8&B8,A2:A7&B2:B7,C2:C7)
Step 3: Enter the XLOOKUP formula Once you have set up the XLOOKUP formula, you can enter it into a cell in your worksheet. In this example, we will enter the formula into cell C8.
After entering the formula, press Enter on your keyboard. The result will be the sales value for John in February, which is 250.
Step 4: Check the result Finally, you can check the result to make sure the XLOOKUP function worked correctly. In this case, we can verify that the sales for John in February is indeed 250 by looking at the example table above.
Note: The XLOOKUP function may return an #N/A error if it cannot find a matching value. You can use the IFERROR function to display a custom message instead of the error message.
Conclusion: XLOOKUP with two criteria is a powerful tool that allows you to search for values that meet two specific conditions. By following the steps outlined in this tutorial, you can use XLOOKUP with two criteria to search for values in Excel.