Reputation: 23
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:
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.
I've seen similar questions and solutions but I for the life of me can not get it to work.
Upvotes: 2
Views: 1968
Reputation: 12113
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,"")
Upvotes: 0
Reputation: 152660
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
Upvotes: 1