Function that creates matches from an array

[mkeys4]

Hi guys,

The attached workbook is a score card that is trying to match up people based on their score. People with similar scores need to be matched together.

So I'm trying to write a function that does the following:

1. Finds the largest number in the array B2:G2
2. Finds the second largest number in the array B2:G2
3. Outputs both codes that title the columns (the codes in row 1) that these two numbers are in (i.e. matches the corresponding codes together)
4. Finds the third largest number in the array B2:G2
5. Finds the fourth largest number in the array B2:G2
6. Outputs both codes that title the columns that these two numbers are in (i.e. matches the corresponding codes together)
7. Finds the fifth largest number in the array B2:G2
8. Finds the sixth largest number in the array B2:G2
9. Outputs both codes that title the columns that these two numbers are in (i.e. matches the corresponding codes together)




Game Data.xlsx

[daffodil11]

How do you propose to break ties? The second and third highest values are the same.

The array formula =INDEX($B$1:$G$1,LARGE(IF(LARGE($B$2:$G$2,ROW(A1))=$B$2:$G$2,COLUMN($A$2:$F$2)),ROW(A1))) pulls the first match of the nth largest value, whereas the non-arrray =LOOKUP(2,1/(LARGE($B$2:$G$2,ROW(A1))=$B$2:$G$2),$B$1:$G$1) aligns to the last match of the nth largest value.

For example:

First Match
=INDEX($B$1:$G$1,LARGE(IF(LARGE($B$2:$G$2,ROW(A1))=$B$2:$G$2,COLUMN($A$2:$F$2)),ROW(A1))) = F1 = 7
=INDEX($B$1:$G$1,LARGE(IF(LARGE($B$2:$G$2,ROW(A2))=$B$2:$G$2,COLUMN($A$2:$F$2)),ROW(A2))) = B1 = 5

Last Match
=LOOKUP(2,1/(LARGE($B$2:$G$2,ROW(A1))=$B$2:$G$2),$B$1:$G$1) = F1 = 7
=LOOKUP(2,1/(LARGE($B$2:$G$2,ROW(A2))=$B$2:$G$2),$B$1:$G$1) = D1 = 5