# Concatenate Index Small Row

#### croodsimian

##### New Member
Guys,

So I'm using and index, small, row formula to find the nth occurrence of a result.

I'm using the below formula to concatenate the results.

{=CONCATENATE(INDEX(Table7[REPAIR NEEDED],SMALL(IF(Table7[ACTION]=\$A\$22,ROW(Table7[ACTION])-ROW(E24),""),1)),INDEX(Table7[REPAIR NEEDED],SMALL(IF(Table7[ACTION]=\$A\$22,ROW(Table7[ACTION])-ROW(E24),""),2)),INDEX(Table7[REPAIR NEEDED],SMALL(IF(Table7[ACTION]=\$A\$22,ROW(Table7[ACTION])-ROW(E24),""),3)))}

However, it only works if there are as many results as nth statements. So if i only have 1 or 2 occurrences, this formula does not work.

Can someone please offer a better formula?

Thanks.

Please let me know if my question is not clear enough.

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### DanteAmor

##### Well-known Member
Try this:

{=IFERROR(INDEX(Table7[REPAIR NEEDED],SMALL(IF(Table7[ACTION]=\$A\$23,ROW(Table7[ACTION])-ROW(E24),""),1))," ")&IFERROR(INDEX(Table7[REPAIR NEEDED],SMALL(IF(Table7[ACTION]=\$A\$23,ROW(Table7[ACTION])-ROW(E24),""),2))," ")&IFERROR(INDEX(Table7[REPAIR NEEDED],SMALL(IF(Table7[ACTION]=\$A\$23,ROW(Table7[ACTION])-ROW(E24),""),3)),"")}

#### croodsimian

##### New Member
Thanks Dante, I was actually able to figure it out last night after posting.

You're welcome

Replies
10
Views
132
Replies
4
Views
210
Replies
10
Views
181
Replies
5
Views
280
Replies
1
Views
115

1,109,367
Messages
5,528,271
Members
409,813
Latest member
robyrux

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...