Index & Match (list more than one match)

[Rudipoo]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]HA Test 1[/TD]
[TD]HA Test 2[/TD]
[TD]HA Test 3[/TD]
[TD][/TD]
[TD]Criteria[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jim[/TD]
[TD]10/09/17 (C)[/TD]
[TD]10/09/17 (A)[/TD]
[TD]10/09/17 (C)[/TD]
[TD][/TD]
[TD]HA Test 1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jane[/TD]
[TD]10/09/17 (A)[/TD]
[TD]10/09/17 (C)[/TD]
[TD]10/09/17 (A)[/TD]
[TD][/TD]
[TD]10/09/17 (A)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Roger[/TD]
[TD]10/09/17 (A)[/TD]
[TD]10/09/17 (C)[/TD]
[TD]10/09/17 (C)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Tim[/TD]
[TD]10/09/17 (A)[/TD]
[TD]10/09/17 (A)[/TD]
[TD]10/09/17 (C)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hi All,

I have a problem that i cant resolve. (i have looked on this site but unable to find)

I am trying to list out names relating to criteria which = "HA Test 1" & "date" & "(A)", i can find 1 name using: =INDEX($A$2:$A$5,MATCH($F$3,INDEX($B$2:$D$5,0,MATCH($F$2,$B$1:$D$1,0)),0),0) which will = Jane but i also want the formula to show the next 2 (Roger & Tim). Therefore when changing the criteria to "HA Test 2" it will return Jim & Tim.

I have tried using: =IF(ROWS($H$13:H13)>$J$13,"",INDEX($A$2:$A$5,SMALL(IF($B$2:$D$5=$F$3,ROW($B$2:$D$5)-ROW(B$2:D$2)+1),ROWS($H$13:H13)))) with the use of a helper cell = $J$13 (this would only relate to the number of names that meet that criteria).

Any help would be great,

Regards

Rudi

 

[Aladin Akyurek]

[TABLE="class: grid, width: 649"]
<tbody>[TR]
[TD][/TD]
[TD]HA Test 1[/TD]
[TD]HA Test 2[/TD]
[TD]HA Test 3[/TD]
[TD][/TD]
[TD]Criteria[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]10/09/17 (C)[/TD]
[TD]10/09/17 (A)[/TD]
[TD]10/09/17 (C)[/TD]
[TD][/TD]
[TD]HA Test 1[/TD]
[TD]Jane[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]10/09/17 (A)[/TD]
[TD]10/09/17 (C)[/TD]
[TD]10/09/17 (A)[/TD]
[TD][/TD]
[TD]10/09/17 (A)[/TD]
[TD]Roger[/TD]
[/TR]
[TR]
[TD]Roger[/TD]
[TD]10/09/17 (A)[/TD]
[TD]10/09/17 (C)[/TD]
[TD]10/09/17 (C)[/TD]
[TD][/TD]
[TD][/TD]
[TD]Tim[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD]10/09/17 (A)[/TD]
[TD]10/09/17 (A)[/TD]
[TD]10/09/17 (C)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In G1 control+shift+enter, not just enter, and copy down:

=SUM(IF(INDEX($B$2:$D$5,0,MATCH(F$2,$B$1:$D$1,0))=F$3,1))

In G2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($G$2:G2)>$G$1,"",INDEX($A$2:$A$5,SMALL(IF(INDEX($B$2:$D$5,0,MATCH(F$2,$B$1:$D$1,0))=F$3,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($G$2:G2))))

 

[Rudipoo]

Hi Aladin,

Thank you for the quick response, think i was almost there lol in a round about way.

This is what i needed, again thank you for answering thread.

 

[mart37]

=if(rows($h$13:h13)>sumproduct(($b$2:$d$5=$f$3)*($b$1:$d$1=$f$2)),"",index($a$2:$a$5,sumproduct(large(($b$2:$d$5=$f$3)*($b$1:$d$1=$f$2)*(row($b$2:$d$5)-row(b$2:d$2)+1),rows($h$13:h13)))))

 

[Aladin Akyurek]

Hi Aladin,

Thank you for the quick response, think i was almost there lol in a round about way.

This is what i needed, again thank you for answering thread.

Glad to help. Thanks for the update.

 

[Rudipoo]

Hi Mart37,

Thank you for replying, using your formula also brings back the same result.