How to find value with two or multiple criteria in Excel?

Suppose you are working with a fruit sales table like the one shown below. You may need to look up the sales amount based on multiple pieces of information, such as the fruit type, sale date, and weight. Employing array formulas in Excel allows you to retrieve these values efficiently, even when you have several conditions to meet. This method is flexible and can adapt to datasets where you need to find a single cell value that matches several criteria.

Array formula 1: Find value with two or multiple criteria in Excel

The general structure of this array formula is as follows:

{=INDEX(array,MATCH(1,(criteria1=lookup_array1)*(criteria2= lookup_array2)…*(criteria n= lookup_array n),0))}

For example, if you want to find the sales amount of mango sold on 9/3/2019, enter the following formula into a blank cell and press Ctrl + Shift + Enter to confirm it as an array formula:

=INDEX(F3:F22,MATCH(1,(J3=B3:B22)*(J4=C3:C22),0))

Note: In this example,

• F3:F22 is the 'Amount' column from which you want to retrieve the value.
• B3:B22 is the 'Date' column; C3:C22 is the 'Fruit' column.
• J3 is the date chosen as the first criterion; J4 is the fruit name used as the second criterion.

Make sure these ranges have the same number of rows; otherwise the formula will return an error.

Adding more criteria is straightforward. For example, to search for the sales amount of mango on 9/3/2019 with a weight of 211, add the third condition to both the MATCH and the lookup arrays as shown:

=INDEX(F3:F22,MATCH(1,(J3=B3:B22)*(J4=C3:C22)*(J5=E3:E22),0))

After entering the formula, again press Ctrl + Shift + Enter to confirm. The result will be the sales amount that meets all specified criteria.

Array formula 2: Find value with two or multiple criteria in Excel by concatenation

Alternatively, you can use concatenation in your formula for a different approach, especially if you want a compact structure. The basic formula is:

=INDEX(array,MATCH(criteria1& criteria2…& criteriaN, lookup_array1& lookup_array2…& lookup_arrayN,0),0)

For instance, to retrieve the sales amount for a fruit with a weight of 242 on 9/1/2019:

=INDEX(F3:F22,MATCH(J3&J4,B3:B22&C3:C22,0),0)

Note: Here,

• F3:F22 is the Amount column; B3:B22 is the Date; E3:E22 is the Weight column.
• J3 is the date; J5 is the weight value for your criteria.

Always keep the order of criteria and corresponding lookup arrays consistent, or the formula may return incorrect results.

For more than two criteria, expand both the criteria and the lookup arrays in the same order:

=INDEX(F3:F22,MATCH(J3&J4&J5,B3:B22&C3:C22&E3:E22,0),0)

Just like before, press Ctrl + Shift + Enter to get the correct result.

Both array formula methods let you find the first value that meets all your criteria. However, they require the cell ranges to be of the same size and do not return multiple matching values—only the first match will be retrieved. If there is no match, the formula returns a #N/A error. If you prefer a formula that displays all matches, consider exploring the FILTER function (see below for details).

Some practical tips and notes:

• If working with newer versions of Excel (Microsoft 365, Excel 2021), you can use dynamic array formulas and the FILTER function to simplify this process.
• To avoid #N/A errors when there are no matches, you might wrap the formula in IFERROR, e.g., =IFERROR(INDEX(F3:F22,MATCH(1,(J3=B3:B22)*(J4=C3:C22),0)),"Not found").
• Double-check that your lookup criteria cells do not have extra spaces or different data types.
• If you receive an error after pressing just Enter, ensure you use Ctrl + Shift + Enter to confirm as an array formula (for Excel 2019 and earlier).

In addition to using formulas, Excel provides the Advanced Filter feature, which allows you to filter and extract all rows that meet two or more criteria, displaying the matching results in another location. This approach is especially helpful when you want to see all records that satisfy your specified conditions, rather than retrieving just a single value. Here’s how to use it:

1. Go to the Data tab and choose Advanced under the Sort & Filter group to open the Advanced Filter dialog box.

2. In the Advanced Filter dialog, complete the following settings:
(1) Select Copy to another location in the Action section.
(2) For List range, highlight the range containing the data you wish to filter (A1:E21 in this example).
(3) For Criteria range, select the range containing your filtering conditions (H1:J2 here). Ensure that the headers in this criteria range exactly match those in your data table.
(4) In Copy to, select the first cell where you wish to paste the filtered results (H9 in this case).

3. Click OK to perform the filtering.

The rows that meet all the conditions in your criteria range will be copied to the destination area you specified. This is especially useful for reviewing or reporting all records that match multiple filter criteria at once.

Some tips and precautions:

• Ensure your criteria range headers are identical to those in your main data table, or the filter may not work properly.
• Advanced Filter supports both AND and OR conditions; placing criteria in the same row applies the AND logic (all must be true), while using separate rows applies OR logic (any can be true).
• The Advanced Filter does not dynamically update when your data changes; you must reapply the filter after updating your data or criteria.
• Be aware that blank cells in the criteria range may be treated as “match any value” for that field.

Compared to formula-based solutions, Advanced Filter is particularly suited to extracting full datasets matching the criteria, rather than just one cell. However, it's not suitable for real-time or frequently updated lookups, as you need to re-execute the filter after data changes.

If you are using a recent version of Excel (Microsoft365 or Excel2021 and later), the FILTER function offers a dynamic and intuitive way to extract all values that meet multiple criteria. This solution is highly recommended for those who need results to update automatically as data or criteria change, and it does not require complex array entry.

1. In a blank cell, enter a formula similar to the following:

=FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4))

In this formula:

• F3:F22 is your Amount column.
• B3:B22 is the Date column, matched to the date in J3.
• C3:C22 is the Fruit column, matched to the fruit in J4.

If you want to add a third condition, such as matching the Weight column (E3:E22) to a value in J5, expand the formula:

=FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4)*(E3:E22=J5))

After pressing Enter, Excel will display all Amounts that satisfy all criteria. If no match is found, the formula will return a #CALC! error, which you can handle using IFERROR:

=IFERROR(FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4)*(E3:E22=J5)), "No match")

Advantages:

• Results update automatically as your data or criteria change.
• Formulas are easier to maintain and expand than older array formulas.
• Returns all matches, not just the first found value.

Limitation: Only available in Microsoft 365, Excel 2021, or later. Not supported in older versions.