Array Formula for 2nd occurrence

[jwehrly2000]

Hello, I have the following formula:

=IFERROR(INDEX(Matrix!A4:A98,MATCH("DNA",Matrix!F4:F98,0)),"")

This formula works perfectly and searches the array for DNA and returns the appropriate cell value. However I need a separate formula to search for 2nd,occurrence and not return the value that has already been returned. I have fiddled around with it for awhile but I have been unsuccessful. Any help would be greatly appreciated.

V/r
Jeremy

 

[Eric W]

Try:

=IFERROR(INDEX(Matrix!A4:A98,SMALL(IF(Matrix!F4:F98="DNA",ROW(F4:F98)-ROW(F4)+1),2)),"")
confirmed with Control+Shift+Enter.

Note that if you want to find the nth occurrence, you can put this formula in a cell, say B2,

=IFERROR(INDEX(Matrix!$A$4:$A$98,SMALL(IF(Matrix!$F$4:$F$98="DNA",ROW($F$4:$F$98)-ROW($F$4)+1),ROWS($B$2:$B2))),"")

confirm with Control+Shift+Enter, then drag it down the column, it will return the first, second, third, etc. instances.

 

[Peter_SSs]

.. or there is this alternative that does not require the Ctrl+Shift+Enter confirmation.

My example is also for the formula to go in cell B2 (change the red part if a different cell is used) and copied down to return the 1st, 2nd, 3rd, etc instance.

=IFERROR(INDEX(Matrix!A$4:A$98,AGGREGATE(15,6,(ROW(Matrix!A$4:A$98)-ROW(Matrix!A$4)+1)/(Matrix!F$4:F$98="DNA"),ROWS(B$2:B2))),"")

Edit: @Eric
I think you would need all your F4:F98 ranges referenced to the Matrix sheet otherwise row insertion/deletion in either Matrix or the formula sheet could cause inaccurate results.

 

[jwehrly2000]

Hello, the formula doesn't seem to be working, In my workbook I have cells I68, J68, I69, J69, I70 and J70 assigned for this. I need I68 = 1st occurrence, J68=2nd, I69=3rd, J69=4th, I70=5th and J70 =6th. Is it possible to do this with this formula. When I inserted it, it would only give the first occurrence. Thanks for all your help.

V/r
Jeremy

 

[Peter_SSs]

Since the formula is being copied both across and down in the I68:J70 range, it needs some adjustments. Try this in I68, copied across to J68 and down to row 70.

=IFERROR(INDEX(Matrix!$A$4:$A$98,AGGREGATE(15,6,(ROW(Matrix!$A$4:$A$98)-ROW(Matrix!$A$4)+1)/(Matrix!$F$4:$F$98="DNA"),2*(ROWS(I$68:I68)-1)+COLUMNS($I68:I68))),"")