Prioritized Index/Match Function with multiple criteria

[oniwlmh]

It appears that I can now attach workbooks so I've included the document which has the dataset and formulas (much easier to see in excel than what I pasted below).

I have a data set of multiple stores that have deliveries of different items each month. Each delivery for a store is represented by a separate row and an "X" in the column for the month if the item is delivered that month. I want to summarize the data into a single row for each store and replace the "X" for the monthly delivery with the actual product delivered based on a prioritization of the products. For example: The prioritization (high to low) is (1) Banana, (2) Apple and (3) Carrots. Store 100 has only Apples delivered Month 1, Bananas and Carrots delivered Month 2, and Bananas, Apples and Carrots delivered Month 3. The output would be a single row for Store 100 that shows "Apple" in the Month 1 column, "Banana" in the Month 2 column (higher priority than carrots) and "Banana" in the Month 3 column (higher priority than both Apples and Carrots).

The formula I currently use just steps through the priorities one at a time using a combined If statement with an array index/match (requiring SCE). I index the "x" marks in the Month (D3:D9 for month one) and then match the store ID and the first priority (H3 = Banana). If it doesn't find an "X" in the first priority, it keeps looking (H4 = Apple).

The formula works as long as the store has a row for each possible delivery. However, if a store does not have one of the items for delivery and it gets to that portion of the if formula, the index/match formula creates an error.

I can't post the actual formula because the thread believes it's a link. I also can't attach the workbook because I haven't posted enough times. Here's the summarized formula I'm using:

=IF(INDEX(Month 1 Column,MATCH("Store ID" & "Priority 1 (Banana)","Dataset Store ID" & "Dataset Item Delivered",0))="X","Banana",IF(INDEX(Month 1 Column,MATCH("Store ID" & "Priority 2 (Apple)","Dataset Store ID" & "Dataset Item Delivered",0))="X","Apple",IF(INDEX(Month 1 Column,MATCH("Store ID" & "Priority 3 (Carrots)","Dataset Store ID" & "Dataset Item Delivered",0))="X","Carrots","")))

[Bo_Ry]

Please try at K3
=INDEX($H$3:$H$5,AGGREGATE(15,6,MATCH($C$3:$C$9,$H$3:$H$5,)/($B$3:$B$9=$J3)/(D$3:D$9="x"),1))

or Table reference
=INDEX($H$3:$H$5,AGGREGATE(15,6,MATCH(INDEX(Table1,,2),$H$3:$H$5,)/(INDEX(Table1,,1)=$J3)/(Table1[Month 1]="x"),1))