Index-Small (Failed to Replicate Example)

Dear All,

I recently tried to replicate an example from a website but failed to get the same results.

FYI, I'm using the "INDEX-SMALL Combination". By the input of "Action" and "Hugh Jackman", I should have obtained "Logan", "The Wolverine" and "Real Steel" as per the example. However, I only got "Prisoners" and nothing else.

Please refer my attached excel file for further info, the website is inside the file since I'm not allowed to post any links yet.

Thanks!

K4=IFERROR(INDEX($B$2:$B$17,AGGREGATE(15,6,ROW($B$2:$B$17)-ROW($B$2)+1/($C$2:$C$17=$K$2)/($D$2:$D$17=$K$3),ROWS($A$1:A1))),"")


Copy down

Copy pasted your formula.. still got "Prisoners"..

for me works

Cell H4 array formula , Drag down

Formula:

Please Login or Register  to view this content.

OR

Formula:

Please Login or Register  to view this content.

Sorry I'm back.. Yes it worked thanks..

I've looked up the "Aggregate" function and kinda understand it from the video, but not the one inside my excel sheet hahaha..

AGGREGATE(15,6,ROW($B$2:$B$17)/(($C$2:$C$17=$H$2)*($D$2:$D$17=$H$3))
- Why function num = 15(small)?
- And I believe ($C$2:$C$17=$H$2)*($D$2:$D$17=$H$3) is referring to the conditions required?

Anyway I've posted an example on what I actually need to do in the new "Example" excel file.

Basically I need the cell to automatically choose the types (A,B,C,D) required because I wanna separate and group them, then will use vlookup to find the "Title" based on the returned "Ref no.".
E.g. For Type A table, it will have to return Ref no. 1,5 and then 9 in the subsequent cells, then vlookup should return their respective title.

Thank you.

one formula

F4=IFERROR(INDEX(INDEX($A$4:$C$13,,MATCH(F$3,$A$3:$C$3,0)),AGGREGATE(15,6,ROW($A$4:$A$13)-ROW($A$4)+1/($B$4:$B$13=$F$2),ROWS($A$1:A1))),"")

Copy across and down

Managed to get my cells working nicely.

Thanks to everyone who helped!