Index & Match (list more than one match)

[Rudipoo]

	A	B	C	D	E	F
1		HA Test 1	HA Test 2	HA Test 3		Criteria
2	Jim	10/09/17 (C)	10/09/17 (A)	10/09/17 (C)		HA Test 1
3	Jane	10/09/17 (A)	10/09/17 (C)	10/09/17 (A)		10/09/17 (A)
4	Roger	10/09/17 (A)	10/09/17 (C)	10/09/17 (C)
5	Tim	10/09/17 (A)	10/09/17 (A)	10/09/17 (C)

<tbody>
</tbody>

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]

	HA Test 1	HA Test 2	HA Test 3		Criteria	3
Jim	10/09/17 (C)	10/09/17 (A)	10/09/17 (C)		HA Test 1	Jane
Jane	10/09/17 (A)	10/09/17 (C)	10/09/17 (A)		10/09/17 (A)	Roger
Roger	10/09/17 (A)	10/09/17 (C)	10/09/17 (C)			Tim
Tim	10/09/17 (A)	10/09/17 (A)	10/09/17 (C)

<tbody>
</tbody>

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.