# Thread: How to show a list of top 10 pairing names from far left column and top row

1. ## How to show a list of top 10 pairing names from far left column and top row

Hi

I was trying to figure out a formula that can be used to show a list of the top 10 pairing names, along with their % value, from the far left column and top row based on the % data in the grid like in this example below:

Name Adam S Jane L Lee M Tim E Ann D

Sarah B 10% 47% 11% 85% 61%
Tom D 2% 19% 0% 20% 15%
Adrian T 27% 71% 43% 57% 8%
Peter M 0% 99% 6% 13% 33%
Amy O 95% 18% 55% 30% 2%

I know Hlookups and Vlookups would not suffice as they would return the value at interception rather than the reverse I'm looking for.

I hope someone can help me.

Kind regards
Chi

Originally Posted by Chi
I hope someone can help me.
You should set an example of your workbook and the expected results. It would be clearer.
Does anyone else have to enter data for you to help you?

For the names from the rows, set the ARRAY formula (copy down)
Code:
`=IF(ROWS(A\$1:A1)>COUNTIF(\$B\$2:\$F\$6;\$I\$1);"";INDEX(\$A\$2:\$A\$6;SMALL(IF(\$B\$2:\$F\$6=\$I\$1;ROW(\$B\$2:\$F\$6)-ROW(\$B\$2)+1);ROWS(A\$1:A1))))`
For the names from the columns, set ARRAY the formula (copy down)
Code:
`=IF(I2="";"";INDEX(\$B\$1:\$F\$1;MATCH(\$I\$1;INDEX(\$B\$2:\$F\$6;MATCH(I2;\$A\$2:\$A\$6;0);0);0)))`
Is this a solution for you?

3. The formula provided assumes the pairing names when you enter a % number which is not what I was asking. I've attached a sample of what I'm hoping to achieve.

Thanks
Chi

4. Attach the workbook, not a picture of it.

5. Workbook attached.

Hopefully, I'll have a response from someone who can help me.

6. I hope you aren't suggesting that I can't help or was being unhelpful.

Excel 2016 (Windows) 32 bit
L
M
N
2
Peter M
Jane L
99%
3
Amy O
95%
4
Sarah B
Tim E
85%
5
Jane L
71%
6
Sarah B
Ann D
61%
7
Tim E
57%
8
Amy O
Lee M
55%
9
Sarah B
Jane L
47%
10
Lee M
43%
11
Peter M
Ann D
33%
 Sheet: Sheet1

Excel 2016 (Windows) 32 bit
L
M
N
2
=INDEX(\$A\$2:\$A\$6,SUMPRODUCT((\$B\$2:\$F\$6=N2)*ROW(\$A\$2:\$A\$6))-COLUMN(\$A\$2))
=INDEX(\$B\$1:\$F\$1,SUMPRODUCT((\$B\$2:\$F\$6=N2)*COLUMN(\$B\$1:\$F\$1))-COLUMN(\$B\$2)+1)
=LARGE(\$B\$2:\$F\$6,ROW(1:1))
 Sheet: Sheet1

This will only work properly if there are no duplicate percentages, as per the sample data provided.

7. This is great!

It works a dream but I would need the formula(s) to accommodate % values that appear more than once in the data. I know the sample data I had attached did not show this but this would be very important to appear in the top 10 pairing names.

Is this possible to do?

8. I thought this might be the case. Please provide sample data that properly reflects the real data. If something is 'very important' and you don't include it, then we cannot provide a solution that takes it into account. You need to tell us everything that is important. Try again with the sample, please, showing how you want to deal with matching values, as it is an entirely different problem to solve.

9. I've attached a sample data as requested which shows matching % values.

10. ## Return Row and Column headers if match duplicate values in range

Originally Posted by Chi
shows matching % values.
There is an error in your expected results, so there is one missing value.
Try this formulas below.

For the Rank
Code:
`=IFERROR(LARGE(\$B\$2:\$F\$6;ROW(A1));"")`
For the Row headers (CSE formula)
Code:
`=IFERROR(INDEX(\$A\$2:\$A\$6;SMALL(IF(\$B\$2:\$F\$6=L2;ROW(\$1:\$5));COUNTIF(L\$2:L2;L2)));"")`
For the Column headers (CSE formula)
Code:
`=IFERROR(INDEX(\$B\$1:\$F\$1;1;SMALL(IF(INDEX(\$B\$2:\$F\$6;MATCH(M2;\$A\$2:\$A\$6;0);)=L2;COLUMN(\$A\$1:\$E\$1));1));"")`
Did this help?

Page 1 of 2 1 2 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•