Excel Exclude Data

Lee Jefferies 20 Reputation points
2026-06-24T14:02:43.93+00:00

I have an all time list of World Cup teams in a league table format on excel with the placing in the 'league' based on average points due to the large difference in games played by the 90 nations who historically have played in a World Cup.

The league table is formed using VLOOKUP to copy the data from a dummy table. I want to be able to not show from the 'league' any team that has played less than 25 matches but the 'league' retains correct position numbers. Converting the 'league' to a table doesn't allow this to work.

The dummy table is created using SUMPRODUCT and SUMIF to capture the data from a fixtures list.

Microsoft 365 and Office | Excel | For home | Windows

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Kai-H 20,755 Reputation points Microsoft External Staff Moderator
    2026-07-01T09:29:48.88+00:00

    Hi, Lee Jefferies

    I would not try to do this with the final league table itself. If the table is currently being built with VLOOKUP from a dummy table, the cleaner way is to filter the dummy table first, then rank the filtered results.

    If you have Excel 365 or Excel 2021, it is recommended that you use FILTER with SORTBY, and put the formula outside an Excel Table, because spilled array formulas do not work inside tables.

    For example, assuming your dummy table is in A2:H91, matches played is in column C, and average points is in column E:

    =LET(d,A2:H91,

    f,FILTER(d,INDEX(d,,3)>=25,""),

    s,SORTBY(f,INDEX(f,,5),-1),

    HSTACK(SEQUENCE(ROWS(s)),s))

    This removes teams with fewer than 25 matches, sorts the remaining teams by average points, and then creates fresh position numbers starting from 1. So the ranking will not have gaps.

    You would just need to change the column numbers in the formula to match your actual layout. If you are on an older Excel version without FILTER, then it is suggested that you add a helper column in the dummy table to mark teams with 25+ matches, then base the lookup/ranking only on those marked rows.

    Thank you for your patience in reading, I hope this information has been helpful to you. 

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment."    

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.