Index/Match with duplicate data

I have having difficulty with my formula. I am trying to create a formula that will decipher between the different name of data with the same usage numbers. You can see on my attached pictures that 3 stations have utilized our program 30 times, however, it only identifies the name of the first station rather than continuing down the list. I hope this explanation makes sense. I label one of the columns ACTUAL station # to show the name that should be listed there.

I could really use some formula help on how to fix this. Thank you in advance!


  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 03/02/21

    Hi @Michelle Fayed 

    Hope you are fine, could you please share a screenshot of the original sheet that contains the data table you trying to collect the data from ( after removing or replacing any sensitive information )This will make it easier for me to provide the appropriate answer to your question.

    PMP Certified

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Michelle Fayed

    Since the 2nd, 3rd, and 4th largest numbers are the same (30), the formula is returning the first match it finds, as it's not able to use a unique value as the match criteria.

    Would the data need to be parsed into individual rows? How about using a JOIN(COLLECT formula instead?


    =JOIN(COLLECT(Name$1:Name$73, [2021]$1:[2021]$73, LARGE([2021]$1:[2021]$73, 4), " / ")

    This should bring all three station names that have used your program 30 times into one cell. Let me know if this would work for you!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!