Index, Match, Large, If formula for duplicaftes

breanna514

New Member
Joined
Mar 29, 2022
Messages
2
Office Version
  1. 2021
Platform
  1. Windows
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
1648586347341.png

1648586383858.png

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
1648586431668.png

The 2nd and 3rd student are different.

Any help is appreciated.
 

Attachments

  • 1648586273649.png
    1648586273649.png
    8.4 KB · Views: 15
  • 1648586421770.png
    1648586421770.png
    4.3 KB · Views: 9

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Assuming that the first formula returning the top 3 values is in E2
Excel Formula:
=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.
 
Upvote 0
Hi!

Thank yo
Assuming that the first formula returning the top 3 values is in E2
Excel Formula:
=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!
 
Upvote 0

Forum statistics

Threads
1,223,355
Messages
6,171,614
Members
452,411
Latest member
sprichwort

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top