There are a couple of issues with the way you applied the formula. And honestly, I'm still not sure I understand your requirements.
First, the current formula:
=IFERROR(INDEX(C:C,AGGREGATE(15,6,
ROW(C:C)/
(B$1:B$99=B1)/(COUNTIF(G$1:G1,
$C$1:$C$99)=0),1)),"")
The part in red here should be ROW(C$1:C$99), not ROW(C:C). It needs to match up with the range sizes later in the formula (in blue). This should be the range where your data is. If you want to search the entire column, it would be:
=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(C:C)/(B:B=B1)/(COUNTIF(G$1:G1,C:C)=0),1)),"")
but I do NOT recommend this. It will really slow down your sheet. If you think your range might have a 1000 rows, use 2000 for the end row, that'll still be much better than searching over a million rows.
Second issue:
=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(C$1:C$99)/(B$1:B$99=
B1)/(COUNTIF(G$1:G1,C$1:C$99)=0),1)),"")
the part in red should be
(B$1:B$99=
B$1)
without the $ in front of the 1, that value will change to B2, B3, etc. as you drag the formula down the column. That's why you get some blank rows. It's searching for some duplicates for a different value, but there aren't any new duplicates. So the corrected formula is:
Excel Formula:
=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(C$1:C$99)/(B$1:B$99=B$1)/(COUNTIF(G$1:G1,C$1:C$99)=0),1)),"")
Change the 99 to whatever is the bottom row of your data.
Hopefully, that will fix your problems. If not, please manually figure out what results you want for the sample above, and show a mini-sheet with those results. If you have another example with expected results, that will help too.
