Index Match Duplicate Values With No Helper Cells

I am using the Match/Index/Large functions in a range that has duplicate values which returns only the first value encountered meeting the criteria. I want to index the second duplicate value and also the third and fourth duplicate value without using helper cells. Every solved example of this problem I have found uses helper cells.

See the attached sample sheet, What I want is in row 5 which returns the 3 items in row 2 that have the value of 15. What I get with a standard Index/Match formula is in row 7 which returns the same item 3 times (the first item encountered with a value of 15).

The order that the duplicates are returned does not matter.

Thanks

Try this array formula** entered in A5 and copied across:

=INDEX($A1:$H1,MATCH(LARGE($A2:$H2-COLUMN($A2:$H2)/10^10,COLUMNS($A5:A5)),$A2:$H2-COLUMN($A2:$H2)/10^10,0))

You're welcome. Thanks for the feedback!

Thanks!

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread