Needing to View Those Users Who Have More Than 10

[ioswoody]

Needing on the front tab (Date) to tell me if the user has reached the threshold of 10 from the data on the second tab (Minimum 10)

There can be up to as many as 300 users in the list

I was using a VLOOKUP and only focusing on the data that was over 10, just wondering if there was a way to focus on the complete data and only return results for those that have reached the minimum of 10

[daffodil11]

B2:

=IFERROR(INDEX('Minimum 10'!A$4:A$10,SMALL(IF('Minimum 10'!$B$4:$B$10>=10,ROW('Minimum 10'!$B$4:$B$10)-ROW('Minimum 10'!$B$4)+1),ROWS($1:1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

and then copy over and down

[ioswoody]

@daffodill11 The Minimum 10 list will have the users in alphabetical order and the front tab will have the uses in order of their Rank. Would the current formula still work in this scenario?

[daffodil11]

I see. Things get complicated..

How do you propose to break ties, should they occur?

[ioswoody]

I think I just need to hide a workings column, I can't see a way to do it all in the one

Added a New Column [D] and it has this =IFERROR(INDEX('Minimum 10'!$A$4:$F$241, MATCH(B2,'Minimum 10'!$A$4:$A$241,0),6),0)
I have needed to stretch out the data on the Minimum 10 Tab

Then changed Column C to do this =IF(D2=9,"1 to Qualify",IF(D2>=10,"Qualified"," "))
I get the result, however Excel says there is an error in the above?

[daffodil11]

On Minimum 10 I create a rank at C4:

=RANK(B4,$B$4:$B$10)+COUNTIF($B$4:B4,B4)-1

and copied down

On the first sheet then
A2:
=IF(B2<>"",ROW(A1),"")
B2:
=IFERROR(LOOKUP(2,1/(('Minimum 10'!$B$4:$B$10>=10)*('Minimum 10'!$C$4:$C$10=ROW(A1))),'Minimum 10'!$A$4:$A$10),"")
C2:
=IFERROR(LOOKUP(2,1/(('Minimum 10'!$B$4:$B$10>=10)*('Minimum 10'!$C$4:$C$10=ROW(A1))),'Minimum 10'!$B$4:$B$10),"")

and copy all three down as far as you want

[FDibbins]

I did this using a helper.

on Min 10 sheet, put this in C4, copied dwon...
=IF(B4<10,"",B4+COUNTIF($B$4:$B4,B4)/100)

Then Date B2...
=IFERROR(INDEX('Minimum 10'!A$4:A$12,MATCH(LARGE('Minimum 10'!$C$4:$C$12,$A2),'Minimum 10'!$C$4:$C$12,0)),"")
copied down and across