Formula help

[Emmily]

Hi

I am after a formula that will return highlighted data G2:I4 if the criteria is jemma

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Test1	Test2	Test3		Criteria		Test1	Test2	Test3
2	apple	yellow	jemma		jemma		apple	yellow	jemma
3	pear	red	tracy				water	red	jemma
4	bear	blue	jack				banana	red	jemma
5	water	red	jemma
6	banana	red	jemma
7	strawberry	black	jack
Sheet2

 

[Jon von der Heyden]

Hi Emmily

Nice to see you about here again.

I don't have a HTML maker installed on this machine, so written instructions instead.

Enter numbers 1;2;3;4;5;6 in G2:G7 (you can make the list larger if you will be dealing with larger range). Or you can use formula:
=ROW()-1 in G2 and copied down.

In H2 enter:
=IF($G2>COUNTIF($C$2:$C$7,$E$2),"",INDEX(A$2:A$7,SMALL(IF($C$2:$C$7=$E$2,$G$2:$G$7),$G2),1))

And confirm with Control+Shift+Enter, not just enter (this is an array formula).

Copy the formula in H2 across and down to J7

 

[Emmily]

Thanks Jon, that did the trick

I thought there might have been another way to incorporate step 1 into step 2 by using one single formula rather than 2.

 

[Jon von der Heyden]

Thanks Jon, that did the trick

I thought there might have been another way to incorporate step 1 into step 2 by using one single formula rather than 2.

It can be done

=IF(ROW()-ROW($A$1)>COUNTIF($C$2:$C$7,$E$2),"",INDEX(A$2:A$7,SMALL(IF($C$2:$C$7=$E$2,ROW($A$2:$A$7)-ROW($A$1)),ROW()-ROW($A$1)),1))

 

[Emmily]

Awesome Jon, you're a star!!!