Alternate to Index+Match functions with duplicate values

[FALGESH]

I'm trying to pull data frm one sheet (~16000 rows) to another sheet, making use of Index and Match function from a cell (WHREIN i'm making use of function LARGE/SMALL).

The problem is, where a cell content is identical in two or more cells (where LARGE/SMALL function is used), the Index + Match Functions returns the first value that it finds in the range.

Is there any otherway round whereby, i can pull all the relevant data

Illustration

A B C D E
1 ABC DEF 36 GEF HIJ
2 PQR WZX 54 POL MNB
3 AQW WER 36 TYU GFR
4 CDF VFG 48 NJM MKL
5 CDS XSD 40 GHJ UIO
6 ZXC VBN 36 BHU OKM

As can be seen frm above, in Column C, value of 36 is being repeated on 3 occassions.

Now when i try to pull corresponding data from coulmn A/B/D or E, making use of Index+Match function, it returns value of C1 only even against C3, C6.

Thanks in advance.

[alski]

Hi,

I'm currently trying to work out a similar problem but I'm only trying to find the second instance.

This is what I have so far and it seems to work. I thought you might be interested to see if it fits your purposes.

=MATCH("findstring",OFFSET(A1,MATCH("findstring",A1:A999,0),0,999,1),0)+MATCH("findstring",A1:A999,0)

[DonkeyOte]

I am firmly of the option that it is always better to use "Helpers" in these instances negating the need both for Arrays and/or Volatiles both of which impact performance

Rough example attached based on OP original example...

(Of course if a Pivot Table is viable you should use that...)