INDEX MATCH function not working on filtered pivot

[heytherejem]

Stuck again.

I have a pivot table of people's user ID, name and scores. I'm then using a table to find the top five highest and lowest scorers.
I'm using an index match formula to find the user ID associated to the top and bottom scores.

When the pivot isn't filtered, both tables work fine.
When the pivot is filtered on month, year or both, the bottom five table kicks off. No idea why.

Both formulae for looking up the top and bottom five are identical with the exception of (LARGE(INDEX and (SMALL(INDEX so I don't understand the issue.

My formula, and spreadsheet are attached.

=INDEX($A$9:$A$120,MATCH(SMALL(INDEX($D$9:$D$120+1/ROW($D$9:$D$120),0),$N9),INDEX($D$9:$D$120+1/ROW($D$9:$D$120),0),0))  

[Tsjallie]

When you select a month and/or a year then the table shrinks, but the formula is still looking at the entire table.
That means that the rows between the last row with data and row 120 are empty. But something makes Excel think there some small value in it.
End of it all is that the match function is looking for a value which is not there. That gives an #N/A error.
To avoid these "ghost values" change the formula to:

Please Login or Register  to view this content.

Notice the brackets meaning it's an array formula. Close it with [Ctrl]+[Shift]+[Enter]

[heytherejem]

Great! That's worked perfectly. Thanks! :-)

[Tsjallie]

Glad I could help. Thx for the rep

[h0raz0n]

Hi

Could I continue on this post? I have the same problem
{=INDEX('CM Pvt by ITNO'!$G$6:$G$3138,MATCH('BL-FA'!$C7&'BL-FA'!$E7,'CM Pvt by ITNO'!$B$6:$B$3138&'CM Pvt by ITNO'!$C$6:$C$3138),0)}

Thanks.

Regards
h0raz0n

[AliGW]

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.