# 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

2. ## Return Row header and Column header if match criteria

Originally Posted by Chi
I hope someone can help me.
This is not your first thread.
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
Adam S
95%
4
Sarah B
Tim E
85%
5
Adrian T
Jane L
71%
6
Sarah B
Ann D
61%
7
Adrian T
Tim E
57%
8
Amy O
Lee M
55%
9
Sarah B
Jane L
47%
10
Adrian T
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.

#### Posting Permissions

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