using INDEX and MATCH function to return multiple results with duplicate data

Question

Client Accounts	April	May	June
Client 1	2	0	1
Client 2	1	1	0
Client 3	0	0	0
Client 4	2	2	3
Client 5	2	1	2
Client 6	1	3	0
Client 7	1	0	0

The above table shows the number of product issues reported by each client broken down into months.

The example displays 7 clients.  In reality there are about 600 clients.

Using the INDEX and MATCH functions in a 2nd table; I am trying to display the top 3 clients who have raised the greatest number of queries per month.

The challenge is where duplicate data is found.  For example, in April Client 1, Client 4 and Client 5 all reported 2 issues each, so I would like my 2nd table to return all 3 client names, however, only Client 1 is being returned.

This would provide an absolute to those viewing the 2nd table that only client 1 had the most reported issues in April which of course is not true.

Answer 1

Enter below formula in cell F2 & copy down & to columns G & H:

=IFERROR(INDEX($A$2:$A$100, MATCH(0, INDEX(NOT(B$2:B$100=LARGE(B$2:B$10,MIN(ROW(1:1),3)))+COUNTIF(F$1:F1, $A$2:$A$100),,), 0)), "")

Column F will return multiple matches for the 3 largest values of April - ie. for the three 2s.

Column G will return multiple matches for the 3 largest values of May - ie. for 3, 2, 1 & 1;

Column H will return multiple matches for the 3 largest values of June - ie. for 3, 2 & 1;

In case you want automatic matching of month names ex. in column J you want multiple matches per month name in J1, enter below formula in cell J2 & copy down - in this case you may enter any month name in cell J1 and multiple matches will be returned for that particular month:

=IFERROR(INDEX($A$2:$A$100, MATCH(0, INDEX(NOT(OFFSET($A$2:$A$100,,MATCH(J$1,$B$1:$D$1,0))=LARGE(OFFSET($A$2:$A$100,,MATCH(J$1,$B$1:$D$1,0)),MIN(ROW(1:1),3)))+COUNTIF(J$1:J1, $A$2:$A$100),,), 0)), "")

Presume this is your requirement ... in case your query has been correctly understood.

Regards,

Amit Tandon

www.globaliconnect.com

Answer 2

The trick is to 'mess' with the rank values

First screenshot shows your data

Second shows my ranking table - on the same sheet

Third shows the table you want - on Sheet2

You will need to expand the ranges to capture all 600 clients

best wishes

Answer 3

Thank you Bernard,

This workaround, also works brilliantly.

Appreciate your support.

Gareth

Answer 4

Thank you Amit.  Worked brilliantly.

Much appreciated.

Gareth