INDEX formula error - using SMALL, ROW, MIN

Originally Posted by KAP123

The heart of the formula is :

INDEX(Data!J$2:J$20000,SMALL(IF($A$2=Data!$A20:$A$20000,ROW(Data!$A20:$A$20000)-MIN(ROW(Data!$A$2:$A$20000))+1,"")

I didn't download your file.

If you index the entire column then you don't need to calculate the array of "offset corrections".

INDEX(Data!J:J,SMALL(IF($A$2=Data!$A$20:$A$20000,ROW(Data!$A$20:$A$20000)),ROWS(A$1:A1)))

Here is a shorter formula

Formula:

=IFERROR(IF(INDEX(Data!A:A,SMALL(IF(Data!$A$2:$A$20000=$A$2,ROW(Data!$A$2:$A$20000)),ROWS(B$2:B2)))=0,"Not available",INDEX(Data!A:A,SMALL(IF(Data!$A$2:$A$20000=$A$2,ROW(Data!$A$2:$A$20000)),ROWS(B$2:B2)))),"")

From KAP123's formulas:

Formula:

INDEX(Data!A$2:A$20000,SMALL(IF($A$2=Data!$A1:$A$20000,ROW(Data!$A1:$A$20000)-ROW(Data!$A$2)+1,""),ROW($A1)))

Change to:

Formula:

INDEX(IF(ISBLANK(Data!A$2:A$20000),"Not available",Data!A$2:A$20000),SMALL(IF($A$2=Data!$A1:$A$20000,ROW(Data!$A1:$A$20000)-ROW(Data!$A$2)+1,""),ROW($A1)))

So, the formulas new:

Formula:

=IFERROR(INDEX(IF(ISBLANK(Data!A$2:A$20000),"Not avalibale",Data!A$2:A$20000),SMALL(IF($A$2=Data!$A1:$A$20000,ROW(Data!$A1:$A$20000)-ROW(Data!$A$2)+1,""),ROW($A1))),"")