Excel: Finding next equal match in column

Question

I've googled for a solution to my problem for days and I just can't seem to get my head around how to do this.

I have 28 chickens and I track the eggs each one lays each month. My Excel sheet looks like this:

Current Egg-cel sheet

Column C is populated by the formula:

=LARGE($A$2:$A$29,$B1)

I'm using column B to increment the LARGE function

I want column D to populate with which chicken number laid the quantity in column C.

Obviously INDEX MATCH or VLOOKUP returns the first match for all equal values. How do I find the the next match? i.e.

Egg-cell sheet 2

I've seen similar questions and solutions but I for the life of me can not get it to work.

Answer 1

If you're happy to add a few helper columns, this is a simple way to go:

The formula in column C creates a unique number by bringing together the eggs laid and the chicken number

=--(A2&"."&B2)

Column D is just your LARGE formula,

=LARGE($C$2:$C$4,B2)

Column E just gets the integer part of column D

=INT(D2)

And finally Column F gets the decimal part (chicken number) from column D

=--REPLACE(MOD(D2,1),1,2,"")

Answer 2

Use this formula in D2:

=INDEX(B:B,AGGREGATE(15,6,ROW($A$2:$A$29)/($A$2:$A$29=C2),COUNTIF($C$2:C2,C2)))

And drag down