Copy data from 2 sheets to others based on criteria

caleb911 

I changed George St. cell D7 from a CSE formula to a regular formula that handles both Jack and Liam. The AGGREGATE function requires Excel 2010 or later, and has similar functionality to SMALL but doesn't need the CSE.

Note that the formula returns 0 when the target cell is blank. You may suppress those zeros in columns N:Q by using a Custom number format #;-#;;@. I don't have a good tweak for your conditional formatting in column  R to get the pink color, however.

=IFERROR(
INDEX(Jack!D$7:D$29,
AGGREGATE(15,6,
(ROW(Jack!$C$7:$C$29) - ROW(Jack!$C$7)+1)/(Jack!$C$7:$C$29=$T$4),
ROWS(D$7:D7)
)
),
IFERROR(
INDEX(Liam!D$7:D$29,
AGGREGATE(15,6,
(ROW(Liam!$C$7:$C$29) - ROW(Liam!$C$7)+1)/(Liam!$C$7:$C$29=$T$4),
ROWS(D$7:D7) - COUNTIF(Jack!$C$7:$C$29,$T$4)
)
),""
)
)