show the 2nd data with same value

[Natas20075]

Hi, I need to found a way that will look the columns where the number of time appear and show the name of that EFC. most of the time, we can have the same number for different EFC code and I need to be able to show the 2nd or the third one is happen to the board next to it. here what I explain in the picture. we see that 1C01 and ES10 have the same number but that show in the board only 1C01 2 time. I use the formule "=INDEX(A18:A108,MATCH(LARGE($B$18:$B$108,1),$B$18:$B$108,0))" and "=INDEX(A18:A108,MATCH(LARGE($B$18:$B$108,2),$B$18:$B$108,0))" for the second box but we see the result.

thank you for your help

[#2]

@Natas20075 

Formula always returns the value for first found largest number, i.e. 1C01. To return all of them it's bit another technique with regular formulas, but that better to demonstrate on the sample file. Or, if you are on modern Excel, use FILTER() function.

[#3]

@Natas20075 

Since you didn't provide a sample workbook, I prepared one with a slightly different layout and used the following Array Formula which requires confirmation with Ctrl+Shift+Enter to get the multiple occurrences of EFC Codes if any.

=TEXTJOIN(", ",TRUE,IF($B$2:$B$23=LARGE(IF(FREQUENCY($B$2:$B$23,$B$2:$B$23),$B$2:$B$23),D2),$A$2:$A$23,""))

[#4]

thank you for that quick response, I forget something important, I have the Excel 2013, unfortunately I don't have the nice new futur like "TextJoin", how I can do the same with my version?

[#5 · Accepted solution]

@Natas20075 

Please refer to the attached with a workaround in case you don't have access to the TEXTJOIN function.

[#6]

Thank you very Much!! that is perfect

[#7]

You're welcome @Natas20075! Glad it worked as desired.