Finding a Value Based on Multiple Criteria in Excel

Sometimes, you may need to find a value in a large data set that meets several conditions. For example, you may want to find the price of a specific product in a certain region and month. In this article, we will show you how to use Excel formulas to lookup values with multiple criteria in separate columns or rows. We will also provide some examples and alternative methods to achieve the same result.

The basic idea of looking up values with multiple criteria is to use a combination of functions that can return a value from a range based on one or more conditions. There are different ways to do this, but the most common and versatile one is to use the INDEX and MATCH functions together.

The INDEX function can return a value from a range based on a given row and column number. The syntax is:

=INDEX (array, row_num, [column_num])

Where array is the range from which to return a value, row_num is the row number in the range, and column_num is the optional column number in the range.

The MATCH function can return the relative position of a value in a range that matches a given criterion. The syntax is:

=MATCH (lookup_value, lookup_array, [match_type])

Where lookup_value is the value to find, lookup_array is the range to search, and match_type is the optional number that specifies how to match the value. The match_type can be 1, 0, or -1, meaning:

• 1: find the largest value that is less than or equal to the lookup_value. The lookup_array must be sorted in ascending order.
• 0: find the exact match of the lookup_value. The lookup_array can be in any order.
• -1: find the smallest value that is greater than or equal to the lookup_value. The lookup_array must be sorted in descending order.

By combining the INDEX and MATCH functions, we can create a formula that can return a value from a range based on one or more criteria. The general formula is:

=INDEX (return_range, MATCH (lookup_value, lookup_range, 0))

Where return_range is the range from which to return a value, lookup_value is the value to find, and lookup_range is the range to search.

To lookup values with multiple criteria, we need to modify the lookup_value and lookup_range arguments of the MATCH function. We can use logical operators and array operations to create an array of TRUE and FALSE values that represent the criteria. Then, we can use the MATCH function to find the position of the first TRUE value in the array. The formula becomes:

=INDEX (return_range, MATCH (1, (criteria1 = range1) * (criteria2 = range2) * … , 0))

Where criteria1, criteria2, … are the conditions to be met, and range1, range2, … are the ranges on which the corresponding criteria should be tested.

Note that this is an array formula, which means it must be entered with Ctrl + Shift + Enter. This will enclose the formula in {curly brackets}, which indicate that it is an array formula.

Procedures

To use the INDEX and MATCH functions to lookup values with multiple criteria, follow these steps:

1. Identify the range from which to return a value, and the criteria to be met. For example, if you want to find the price of a large red T-shirt, the return range is the column that contains the prices, and the criteria are the size, color, and product.
2. Enter the INDEX function in a cell, and specify the return range as the first argument. For example, =INDEX (E5:E11, …
3. Enter the MATCH function as the second argument of the INDEX function, and enter 1 as the first argument. This means we want to find the position of the first 1 in the array of criteria. For example, =INDEX (E5:E11, MATCH (1, …
4. Enter the criteria and the ranges as the second argument of the MATCH function, using logical operators and array operations. For example, if the criteria are in cells H5, H6, and H7, and the ranges are in columns B, C, and D, enter (H5=B5:B11) * (H6=C5:C11) * (H7=D5:D11) as the second argument of the MATCH function. This will create an array of 1 and 0, where 1 means the criteria are met, and 0 means they are not.
5. Enter 0 as the third argument of the MATCH function, to indicate that we want an exact match of the criteria. For example, =INDEX (E5:E11, MATCH (1, (H5=B5:B11) * (H6=C5:C11) * (H7=D5:D11), 0))
6. Press Ctrl + Shift + Enter to complete the formula as an array formula. The formula will be enclosed in {curly brackets}, and the result will be displayed in the cell.

Example

Let’s see an example of how to use the INDEX and MATCH functions to lookup values with multiple criteria. Suppose we have the following data set, which shows the sales of different products in different regions and months.

We want to find the sales of pens in the south region in February. We can use the following formula:

=INDEX (D2:D9, MATCH (1, (A2:A9=“South”) * (B2:B9=“Feb”) * (C2:C9=“Pen”), 0))

The formula returns $9, which is the correct answer.

Alternative Methods

Besides using the INDEX and MATCH functions, there are some other ways to lookup values with multiple criteria in Excel. Here are some of them:

• Use the XLOOKUP function, which can return a value from a range based on one or more criteria. The syntax is:

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

Where lookup_value is the value to find, lookup_array is the range to search, return_array is the range from which to return a value, if_not_found is the optional value to return if no match is found, match_mode is the optional number that specifies how to match the value, and search_mode is the optional number that specifies how to search the range.

To lookup values with multiple criteria, we can use the & operator to concatenate the criteria and the lookup_array. For example, the formula above can be rewritten as:

=XLOOKUP (“South”&“Feb”&“Pen”, A2:A9&B2:B9&C2:C9, D2:D9)

This formula also returns $9, which is the same as the INDEX and MATCH formula.

Note that the XLOOKUP function is only available in Excel 365 and Excel 2023.

• Use the FILTER function, which can return a subset of a range that meets one or more criteria. The syntax is:

=FILTER (array, include, [if_empty])

Where array is the range to filter, include is the logical expression that defines the criteria, and if_empty is the optional value to return if no values meet the criteria.

To lookup values with multiple criteria, we can use the * operator to combine the criteria and the include argument. For example, the formula above can be rewritten as:

=FILTER (D2:D9, (A2:A9=“South”) * (B2:B9=“Feb”) * (C2:C9=“Pen”))

This formula also returns $9, which is the same as the INDEX and MATCH formula.

Note that the FILTER function is only available in Excel 365 and Excel 2023.