Index/Match with duplicate data

Michelle Fayed

SS Formual 1.png

SS Formual 2.png

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

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.

Genevieve P.

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?

Ex:

=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!

Cheers,

Genevieve