Calculating values based on position in family

[Zululander]

Our school SMS exports data as follows

Student - Legal Name	Group 1 - Name	Edge Name	Fee
Jon Smith	Auckland Campus	Peter and Sue Smith	0
Sue Smith	Auckland Campus	Peter and Sue Smith	0
Angie Brown	Nelson Campus	James and Jackie Brown	0
Tom Rivers	Nelson Campus	Jon and Judy Rivers	0
Judy Rivers	Nelson Campus	Jon and Judy Rivers	0
Storm Rivers	Nelson Campus	Jon and Judy Rivers	0
Lawson White	Rangiora Campus	Alan and Syliva White	0
Ted Foster	Maungaturoto Campus	Steve and Kim Foster	0
Louis Crawshaw	Hastings Campus	Albert and Peta Crawford	0
Brett Stone	Hamilton Campus	Alan and Sarah Stone	0
Karen Shaw	Auckland Campus	Andre and Emma Shaw	0
David Shaw	Auckland Campus	Andre and Emma Shaw	0
Suzzie Banks	Auckland Campus	Fred and Simon Banks	0
Trevor Banks	Auckland Campus	Fred and Simon Banks	0
Anna Banks	Hastings Campus	Fred and Simon Banks	0
Kate Banks	Hastings Campus	Fred and Simon Banks	0
Phil Green	Hawera Campus	Andrew and Kim  Green	0

We are needing the Fee colum to be auto populated as follows i.e. the First child in the family gets charged Fee1, 2nd Fee2 etc up until Fee5 (Which is the lost fee possible). What forumlar can I use to vreate this.

[#2]

@Zululander 

In D2:

=CHOOSE(MIN(COUNTIF($C$2:$C2,$C2),5),Fee1,Fee2,Fee3,Fee4,Fee5)

Replace Fee1 to Fee5 with the actual fee amounts.

Then fill down.

[#3]

@Hans Vogelaar Thanks for this but I am still doing something wrong.

I used 

=CHOOSE(MIN(COUNTIF($C$2:$C2,$C2),5),$B$1,$B$2,$B$3,$B$4,$B$5)

And get the following

[#4 · Accepted solution]

@Zululander 

I had assumed that the data began in row 2. Your data begin in row 8, so you should use

=CHOOSE(MIN(COUNTIF($C$8:$C8,$C8),5),$B$1,$B$2,$B$3,$B$4,$B$5)

in D8, then fill down.

[#5]

@Zululander 

Alternatively:

=INDEX($B$1:$B$5,MIN(COUNTIF($C$8:$C8,$C8),5))

[#6]

lol, I can't believe I did that thanks :)