CODEWITHSUNDEEP
excelexcel-formulavba
PMC
PMC

Reputation: 49

Multiple Criteria Index/Match/Countif in Excel to return a cell value

I'm looking to pull certain cell values based on several criteria. The best way to describe what I'm looking to do is with this sample image:

enter image description here

This is going to be a massive data set with many more unique options in row 6 and in column B, but this is an example.

Basically, I need to use the two input cells (B2 and B3, which are dropdowns) to find where a "Y" is populated, and return the value in column A that corresponds to that intersection. So, the goal is populating cell E2 with the value where the two drop-downs intersect, AND there is a Y at that intersection (because there are instances where they intersect but there is no "Y").

I've tried various combinations of Index/Match functions with countifs, but I've been getting errors with everything I try.

I have a basic Index/Match that will find the intersection, which isn't tough, but it's incorporating the countif to find where that intersection also has a "Y" that I'm struggling with (and to ignore all intersections that don't have a "Y").

My starting point:

=INDEX($A$6:$E$37,MATCH($B$2,$A$6:$E$6,FALSE),MATCH($B$3,$B$6:$B$37,FALSE))

Any tips are greatly appreciated

Upvotes: 0

Views: 3220

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

As the result is a number you can try:

=SUMIFS(A:A,B:B,B3,INDEX(C:E,0,MATCH(B2,C6:E6,0)),"Y")

Or if the combinations are not unique and it adds two or more line together, or if column a is strings and not numbers then use this to return the first:

=INDEX(A:A,AGGREGATE(15,ROW(A7:A37)/((B7:B37 = B3)*(INDEX(C7:E37,0,MATCH(B2,C6:E6,0))="Y")),1))

Upvotes: 0

Related Questions