Index and Match function to locate a value with multiple criteria

For the given data, I seek to find quantity of green colored apple.

Named ranges fruit_, color_, quantity_ created.

Used this formula:

=INDEX(quantity_,MATCH(1, (Apple = fruit_) * (Green = color_), 0))

Help appreciated where I am going wrong.

@dav:You can also use data validation to create dropdowns for your selections and reference those cells in your formula. Here is the dropdown created using the pivot table.

1 Answer

You can do this with a simple SUMIFS
=SUMIFS(C2:C5,A2:A5,"Apple",B2:B5,"Green")

If you have multiple lines with Apples | Green, then you'll end up with the sum of all the rows, but with a single row you'll get the direct result you've listed.

If your data gets more complicated, you can convert it to a Table and use the column names for better readability. You can also use data validation to create dropdowns for your selections and reference those cells in your formula.

UPDATE:

(not a pivot) Table

In your question update you have added a Pivot Table, while I was suggesting a Table. These are two different approaches, each with their own strengths/weaknesses. To address your original question, I recommend the use of a Table Insert > Table. This has two key advantages:

1. you can use Excel's Structured References, basically Named Ranges on steroids.
2. the ranges automagically grow as rows are added to the table

If applied to your original list, it would look like this:

And assuming you name your table tbl_su19709728, using the Structured References, your formula could be changed to =SUMIFS(tbl_su1709728[count],tbl_su1709728[fruit],"apple",tbl_su1709728[color],"green")

You can also directly filter your rows using the Table's column header filters.

Pivot Table

You can create a Pivot Table and format it very similarly to the Table shown above.

However, you cannot use Structured References in your formula to reference the Pivot Table values. You can use GETPIVOTDATA, however its very complex and unreadable in comparison to Structured References.

Dropdown Selection

Also, as noted, you can use Data Validation to build your formula. This makes a nice user interface and isn't difficult to set up. It is somewhat redundant to just filtering the Table directly (using the Table's Header filters).

Some considerations when using data validation:

1. You cannot directly use Structured References in your Data Validation formula. To use the Structured Reference, you'll need to create a Named Range using the Structured Reference for the column.
2. If your Table's Column has duplicates, your Data Validation list will have the same duplicates. Unfortunately, UNIQUE returns an array value and cannot be used in Data Validation. You can use a Helper Column and OFFSET and INDIRECT to overcome this, but it gets more complicated.
3. It will not catch invalid combinations (e.g. Yellow Grapes). You can prevent this be creating a calculated column that combines Fruit and Color and then run Data Validation on that column, returning only valid combinations.

In then end, you'll need to find the balance between complexity of setup and ease of use.