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

 

[Macropod]

Duplicate of: https://www.mrexcel.com/forum/excel-questions/1022163-index-match-list-more-than-one-match.html

Please read Mr Excel's policy against duplicate posting, in Rule 12: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

Thread closed. You may continue the discussion in your other thread.