# Formula help

#### Emmily

##### Well-known Member
Hi

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

Excel Workbook
ABCDEFGHI
1Test1Test2Test3CriteriaTest1Test2Test3
2appleyellowjemmajemmaappleyellowjemma
3pearredtracywaterredjemma
4bearbluejackbananaredjemma
5waterredjemma
6bananaredjemma
7strawberryblackjack
Sheet2

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### Jon von der Heyden

##### MrExcel MVP, Moderator
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

Last edited:

#### Emmily

##### Well-known Member
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

##### MrExcel MVP, Moderator
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

##### Well-known Member
Awesome Jon, you're a star!!!

Replies
3
Views
210
Replies
2
Views
459
Replies
12
Views
506
Replies
15
Views
1K
Replies
7
Views
413

1,195,924
Messages
6,012,324
Members
441,690
Latest member
CyberWrek

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back