Supposing you have a fruit sales table as below screenshot shown, you need to find out the amount value based on multiple criteria. Here, this method will introduce some array formulas to find out values based on these given criteria easily.

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

The basic expression of this array formula displays as below:

{=INDEX(array,MATCH(1,(criteria 1=lookup_array 1)*(criteria 2= lookup_array 2)…*(criteria n= lookup_array n),0))}

Let’s say you want to find the sales amount of mango occurring on 9/3/2019, you can enter below array formula into a blank cell, and then press Ctrl + Shift + Enter keys together.

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

Note: In above formula, F3:F22 is the Amount column you will find value in, B3:B22 is the Date column, C3:C22 is the Fruit column, J3 is a date given as the first criteria, J4 is fruit name given as the second criteria.

According to the array formula expression, you can easily add criteria as you need. For example, you now are looking for the sales amount of mango occurring on 9/3/2019, and the mango weight is 211, you can add the criteria and lookup_array in the MATCH section as follows:

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

And press Ctrl + Shift + Enter keys to find out the sales amount.

Array formula 2: find value with two or multiple criteria in Excel

The basic expression of this array formula displays as below:

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

For example, you want to find out the sales amount of a fruit whose weight is 242 and occurs on 9/1/2019, you can enter below formula into a blank cell, and press Ctrl + Shift + Enter keys together.

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

Note: In above formula, F3:F22 is the Amount column you will find value in, B3:B22 is the Date column, E3:E22 is the Weight column, J3 is a date given as the first criteria, J5 is weight value given as the second criteria.

If you want to find value based on three or more criteria, you can easily add your criteria and lookup_array into the MATCH section. Please note that the criteria and lookup_array must be in the same order.

For example, you want to find out the sales amount of pear with weight of 242 and occurs on 9/1/2019, you can add the criteria and lookup_array as follows:

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

And press Ctrl + Shift + Enter keys to figure out the sales amount.

Apart from formulas, you can also apply the Advanced Filter feature to find all values with two or multiple criteria in Excel. Please do as follows:

Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 60-day, no credit card required! Get It Now

1. Click Data > Advanced to enable the Advanced Filter feature.

2. In the Advanced Filter dialog, please do as follows:
(1) Check the Copy to another location option in the Action section;
(2) In the List range box, please select the range you will find values in (A1:E21 in my case);
(3) In the Criteria range box, please select the range you will find values by (H1:J2 in my case);
(4) In the Copy to box, please select the first cell of destination range you will place the filtered rows (H9 in my case).

3. Click the OK button.

Now the filtered rows matched with all listed criteria are copied and placed in the destination range. See screenshot: