AGGREGATE/COUNTIF Formula Issue

[excelbytes]

I have this formula in cell E4, then copied down:

=IFERROR(INDEX($B$4:$B$74,AGGREGATE(15,6,(ROW($B$4:$B$74)-ROW(INDEX($B$4:$B$74,1))+1)/($A$4:$A$74=D4)/(COUNTIF($E$3:E3,$B$4:$B$74)=0),1)),"")

In essence, all it's doing is copying the data from column B to column E. I know this sounds unnecessary, but it will be used for other purposes later. I'm just using it here to test out the concept. It's a structure that I learned from Eric W on the forum a couple years ago. As you can see on the attached image, it works fine until row 23, then it's not pulling the correct values. Column G is just comparing B and E. I've determined that the issue is in the COUNTIF portion of the formula, but I don't see what is wrong. Actually, I was never certain how that part of the formula worked, but it has in the past. Can anyone provide any insight as to the issue? Thanks a lot.

The formula in cell E23 is:

=IFERROR(INDEX($B$4:$B$74,AGGREGATE(15,6,(ROW($B$4:$B$74)-ROW(INDEX($B$4:$B$74,1))+1)/($A$4:$A$74=D23)/(COUNTIF($E$3:E22,$B$4:$B$74)=0),1)),"")

 

[KRice]

The formula does not appear to be designed to simply copy B to E, but rather to copy values from B that have not already been listed in E. At row 23, JWH2 Apt 148 is already listed on row 4, so upon seeing JWH2 in D23, the next available and previously unlisted Apt is Apt 164 pulled from row 29. To reach that row, the formula determined that JWH2 apts shown in row 27 (141) and row 28 (151) were also previously listed in E, so they were bypassed.

What are your expectations for the newly created list?

 

[excelbytes]

I see what I was doing wrong. I changed the formula to:

=IFERROR(INDEX('Sales Receipts'!$D$4:$D$74,AGGREGATE(15,6,IF('Sales Receipts'!$B$4:$B$74=A4,ROW('Sales Receipts'!$B$4:$B$74)),COUNTIF($A$4:A4,A4))-3,1),"")

...and it works fine. Thanks for pointing me in the right direction!

 

[KRice]

I'm glad to hear that you got it sorted out.