Forum Discussion

Alex-CHUC's avatar
Alex-CHUC
Copper Contributor
Jan 08, 2025

How to do INDEX MATCH in table with multiple matches

Hi, I am trying to create a table that is basically showing the top 3 results from a another table with 2 columns. However, I've been running into an issue when the values are the same, as it is only returning the first result. I need this table to automatically update monthly when I Change the data in the main table, so my top 3 results may change to more than 3 rows (like say there is two ties, one for first and two for third totaling 5 rows).  Is this possible to do using the current formula I have been using, INDEX MATCH, or is there a different formula?  I tried the FILTER function, and that returns the correct values, however I can't make it into a table. Ideally I'd like to keep my table, but if it's not possible then I will have to change it to a range. I really need this function to work, as It will be time consuming to go through the data to see if there is any tie breakers and have to manually update my top 3 results. I have figured out how to show the second result in a tie breaker, but as I've said above, I need this to automatically add or delete rows from my top 3 results table when the main data is changed, so if I'm showing the results for the top 3, I don't have to go through my main data to find a tie breaker and then have to manually add those rows to the table.  The formula I have in D3 is =INDEX(Table5,MATCH($E3,Table5['# of Exams],0),1) and the only thing that changes for D4 &D5 is the row number in my match formula, so $E4 & $E5.

The formula I have in E3 is =LARGE(Table5['# of Exams],1) and the only thing that changes for E4 & E5 is the last numbers to 2 and .

however for this data I noticed that there is a tie breaker so in cell D6 I have the formula as =INDEX(Table5,MATCH(E5,Table5['# of Exams],2),1). which returned the second result in my table.

However I did try =FILTER(Table5,Table5['# of Exams]=E3), but if the data set is more then 3 rows, then It says Spill and It's the same problem as above, I need it to automatically add rows so that all my results can be shown.

I've been trying to play around with a fix, but nothing is working

Any help would be very much appreciated. 

 

Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Jan 10, 2025

    FILTER is the right idea but you still have to deal with the potential ties.

    This will pull a dynamic top 3:

    =LET(
    M, SORT(DemoTbl, 2, -1),
    total, TAKE(M, , -1),
    top, INDEX(UNIQUE(total), 3),
    FILTER(M, total >= top)
)

    Tables are great for data input but don't work with spilling and dynamic arrays.

Resources