Index Match with multiple results

[exlefo]

Hello all,

I hope that someone more experienced than I in here can help me where I have gotten stuck

I have the following workbook:

In this work book, I have a list of sales in different categories, and I am trying to show a ranked list for a selected Category, what the Sales and the Item was.
However, I have run into a problem, which is that if another category has the same sales, then my Item list gets broken as it returns the first item it sees with that amount of sales

The formulas I am using for sales is:
=AGGREGATE(14;6;$C$5:$C$16*($A$5:$A$16=$F$2);E5)

And the formula I am using for item is:
{=IF(N(F5);INDEX($B$5:$B$16;SMALL(IF(($C$5:$C$16=F5);ROW($B$5:$B$16)-ROW(E5)+E5);COUNTIF($F$5:F5;$F$5)));"")}


I tried solving it by adding to the formula for items, an AND formula to the Small, but this makes all Items return as "Apple"
=IF(N(F5);INDEX($B$5:$B$16;SMALL(IF((AND($C$5:$C$16=F5;$A$5:$A$16=$F$2));ROW($B$5:$B$16)-ROW(E5)+E5);COUNTIF($F$5:F5;$F$5)));"")


Does anyone have any suggestions for how / if this is possible to solve?


Thanks in advance!
Exlefo

 

[Marcelo Branco]

Welcome to Mr Excel forum

To use an AND condition in an array formula you should either use nested IFs or multiply the conditions.

See if this array formula works
=IF(ISNUMBER(F5);INDEX(B$5:B$16;SMALL(IF(A$5:A$16=F$2;IF(C$5:C$16=F5;ROW(B$5:B$16)-ROW(B$5)+1));COUNTIF(F$5:F5;F5)));"")
Ctrl+Shift+Enter

Hope this helps

M.

 

[Fluff]

Try
=IF(N(F5);INDEX($B$5:$B$16;SMALL(IF(($C$5:$C$16=F5)*($A$5:$A$16=$F$2);ROW($B$5:$B$16)-ROW(E5)+E5);COUNTIF($F$5:F5;$F$5)));"")

 

[Marcelo Branco]

Try
=IF(N(F5);INDEX($B$5:$B$16;SMALL(IF(($C$5:$C$16=F5)*($A$5:$A$16=$F$2);ROW($B$5:$B$16)-ROW(E5)+E5);COUNTIF($F$5:F5;$F$5)));"")

Fluff

I think your formula has a typo (in red).
Shouldn't it be?
...COUNTIF(F$5:F5;F5)

M.

 

[Fluff]

Yup, it probably should be.
I didn't look at that part of the Op's formula