Index, Match, Large, If formula for duplicaftes

[breanna514]

Hello,

I am trying to pull the top 3 values and names from a data set (this is essentially top 3 student affirmation earners in each advisory). The problem is it is pulling the same student twice instead of the next student in the data set.

Tab 1: Dashboard
Column A = Grade
Column B = Homeroom
Column C = Rank (1-3)
Column D = Students Name (this is where =ArrayFormula(INDEX('data*'!$A:$A, MATCH(LARGE(if('data*'!$C:$C=$B$11,'data!$D:$D),C$13),if('data!$C:$C=$B$11,'data!$D:$D),0))) is
Column E = Top 3 Values (this is where = =ArrayFormula(LARGE(if('data'!$C$2:$C$365=$B$11,'data'!$D$2:$D$365), ROWS(C$11:C11))) is

Student 2 and 3 is the same student listed twice.

Tab 2: Data
Column A = Student Name
Column B = Grade
Column C = Homeroom
Column D = Values

The 2nd and 3rd student are different.

Any help is appreciated.

 

[jasonb75]

Assuming that the first formula returning the top 3 values is in E2

=ArrayFormula(INDEX('data*'!$A:$A,SMALL(IF('data'!$C$2:$C$365=$B$11,IF('data'!$D$2:$D$365=$E2,ROW('data'!$D$2:$D$365))),COUNTIF($E$2:$E2,$E2)))

For reference, it would help if you tell us what you need the answer provided to work with, your profile says office 2021 whilst your own formulas appear to be for google sheets. There are a number of functions in one that will not work with the other meaning that if it is not clear which one you are using then you may get answers that do not work for what you need.

 

[breanna514]

Hi!

Thank yo

Assuming that the first formula returning the top 3 values is in E2

=ArrayFormula(INDEX('data*'!$A:$A,SMALL(IF('data'!$C$2:$C$365=$B$11,IF('data'!$D$2:$D$365=$E2,ROW('data'!$D$2:$D$365))),COUNTIF($E$2:$E2,$E2)))

For reference, it would help if you tell us what you need the answer provided to work with, your profile says office 2021 whilst your own formulas appear to be for google sheets. There are a number of functions in one that will not work with the other meaning that if it is not clear which one you are using then you may get answers that do not work for what you need.

Hi! Thank you so much for responding. I am sorry for responding so late. Yes, I am using google sheets. I will be sure to specify that in my future posts. This worked beautifully, thank you so much!