Excel help needed please- INDEX and LARGE commands (and maybe an IF)

[Redwinegirl]

Hi

I have a spreadsheet that now shows a league table (top 10 students) and when I filter the main table, my league updates which is great...

I have this type of thing: =INDEX($C$3:$C$1380,MATCH(AA1409,$Y$3:$Y$1380,0))

When I filter say Blue House - in the main table only Blue House students shows but in my league Student 4 comes through. I assume this is because there is no students in Blue House in position 4.

 

 

The issue I have now though is e.g:

Student 1 Blue House 0.5

Student 2 Blue House 0.5

Student 3 Blue House 0.4

Student 4 Red House 0

 

I hope I've explained that ok?

 

any help appreciated

 

Thanks

[LosOjos]

Hi

I have a spreadsheet that now shows a league table (top 10 students) and when I filter the main table, my league updates which is great...

I have this type of thing: =INDEX($C$3:$C$1380,MATCH(AA1409,$Y$3:$Y$1380,0))

When I filter say Blue House - in the main table only Blue House students shows but in my league Student 4 comes through. I assume this is because there is no students in Blue House in position 4.

 

 

The issue I have now though is e.g:

Student 1 Blue House 0.5

Student 2 Blue House 0.5

Student 3 Blue House 0.4

Student 4 Red House 0

 

I hope I've explained that ok?

 

any help appreciated

 

Thanks

 

It sounds like exactly what you descrbed - there are only 4 blue house students, so position 5 is filled by the next highest student in the list - I believe it was me who helped you with this before and using the trick of setting hidden value to 0 to exclude them, right?

 

The simplest way to fix it is to wrap each of your league table formulas in an IF, ending up with something like this:

 

=IF(INDEX($C$3:$C$1380,MATCH(AA1409,$Y$3:$Y$1380,0))>0,INDEX($C$3:$C$1380,MATCH(AA1409,$Y$3:$Y$1380,0)),"")

 

In plain English, this says "if the result of INDEX/MATCH is greater than 0, display the result, otherwise display nothing"

 

You'll have to tweak the column number in your INDEX so that the first call (the comparison) always looks at the value column (to see if it is indeed greater than zero) but the second call returns the data you actually want from the correct column.

[Redwinegirl]

Thanks - it was you that helped enormously thank you! Will try this tomorrow as manic first day back today :-(

 

Thanks!!!!

[Redwinegirl]

Hi

This didn't work unfortunately- I had the inconsistent formula green triangle but the cell still returned a red house student....

However...

I used your logic and in the cell that has the LARGE formula

=IF(LARGE($K$3:$K$1380,C1395)<>0,LARGE($K$3:$K$1380,C1395),"")

This works - it still shows students in the Blue House that have a '0' but doesn't then show the red house student anymore!

 

Thank you so much for your help!!

 

Kay

[LosOjos]

Hi

This didn't work unfortunately- I had the inconsistent formula green triangle but the cell still returned a red house student....

However...

I used your logic and in the cell that has the LARGE formula

=IF(LARGE($K$3:$K$1380,C1395)<>0,LARGE($K$3:$K$1380,C1395),"")

This works - it still shows students in the Blue House that have a '0' but doesn't then show the red house student anymore!

 

Thank you so much for your help!!

 

Kay

 

Glad you got it sorted