Import from master data

No file attached.

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

See attached:

Helper columns AC and AD

AC is list of clients
AD is number of members of each client familiy

AD

=COUNTIF(E:E,AC2)

AE:AF is lookup table for SUM_ASSURED (named range)

in Q2

=IFERROR(INDEX($AC$2:$AC$10,MATCH(0,INDEX(--(COUNTIF(Q$1:$Q1,$AC$2:$AC$10)=$AD$2:$AD$10),0),0)),"")

copy across to W

in AA2

=VLOOKUP(X2,Sum_Assured,2,0)

In O3 then drag down blank cells in column O

=O2

ARRAY formula in P3, then copy to all cells in columns P to T

=IFERROR(INDEX(F$2:F$3211,SMALL(IF($C$2:$C$3211=$O3,ROW($C$2:$C$3211),""),COUNTIF($O$2:$O3,$O3))-ROW($C$2)+1),"")