Using spreadsheet to make a pairwise comparison matrix

Question

I want to use a spreadsheet to take a column of rankings and turning into a pairwise matrix. Suppose I had a column like so:

Candidate	Ranking
A	1
B	5
C	3
D	2
E	4

I want to create a 5x5 matrix where each cell displays a 1 if the row candidate beats the column candidate, and a 0 otherwise, like so:

Pairwise Matrix in Sheets

(I don't know how to make a table of more than 2 columns here, so I used an image.)

I was able to make this table by manually making the first cell of each column individually, using an if statement, and dragging down, but I'd like to make a single formula that can do the whole table at once.

VBasic2008 · Accepted Answer · 2025-09-29 17:58:26Z

4

Row Beats Column

Readable (D1:I6,K1:P6)

=LET(t,A2:A6,c,B2:B6,title,"T",
    h,HSTACK(title,TOROW(t)),
    r,TOROW(c),
    d,IF(r=c,"",IF(r>c,1,0)),
    VSTACK(h,HSTACK(t,d)))

Short (D1:I6,K1:P6)

=LET(t,A2:A6,c,B2:B6,title,"T",
    r,TOROW(c),
    VSTACK(HSTACK(title,TOROW(t)),
        HSTACK(t,IF(r=c,"",IF(r>c,1,0)))))

Matrix Only (E2:I6,L2:P6)

=LET(c,B2:B6,
    r,TOROW(c),
    IF(r=c,"",IF(r>c,1,0)))

Ingredients

K1: =HSTACK("T",TOROW(A2:A6))
K2: =A2:A6
L2: =IF(TOROW(B2:B6)=B2:B6,"",IF(TOROW(B2:B6)>B2:B6,1,0))

answered Sep 29 at 17:58

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

James Cleveland-Tran Sep 30 at 12:04

Oh, of course, to make a 5x5 matrix from a 5x1 you need to "multiply" it by a 1x5, so transposing it (with TOROW) makes perfect sense.

VBasic2008 Sep 30 at 12:38

Thanks for the response. I guess I should have added something like your comment to my post. Sorry about that, and about the dash (-). Also, you can replace each IF(r>c,1,0) with N(r>c) and IF(TOROW(B2:B6)>B2:B6,1,0) with N(TOROW(B2:B6)>B2:B6) as illustrated in P.b's answer to shorten the formulas. Then the matrix formula could look something like this: =LET(c,B2:B6,r,TOROW(c),IF(r=c,"",N(r>c))).

P.b Oct 1 at 20:37

Reason I included A2:A6 in the logic IF(A2:A6=TOROW(A2:A5),"-",...) is that I figured B column values may potentially equal in some cases. The ID's are unique.

VBasic2008 Oct 1 at 22:13

@P.b I never noticed any difference. Now I see that OP said "... and a 0 otherwise...". I disregarded it when I looked at the screenshot, thinking "when equal, show empty string" (not even noticing the dashes). It is easily adjusted to your functionality (same as in the other 2 answers) by replacing r=c with t=TOROW(t). Thanks for pointing that out. I guess I have to increase the font size or start wearing glasses.

P.b Oct 2 at 5:39

Lol, happens to me also.

cybernetic.nomad · Accepted Answer · 2025-09-29 16:16:40Z

2

The following formula will do what you want:

=IF(G$1<>$F2,IF(INDEX($B$2:$B$6,MATCH($F2,$A$2:$A$6,0))<INDEX($B$2:$B$6,MATCH(G$1,$A$2:$A$6,0)),1,0),"-")

example:

answered Sep 29 at 16:16

cybernetic.nomad

6,5763 gold badges20 silver badges36 bronze badges

Comments

P.b · Accepted Answer · 2025-09-29 21:27:10Z

2

Another possible solution:

=LET(a,A2:A6,
     x,TOROW(a),
     b,B2:B6,
     y,TOROW(b),
     h,HSTACK,
VSTACK(h("",x),
       h(a,IF(a=x,"-",N(b<y)))))

answered Sep 29 at 21:27

P.b

12k2 gold badges15 silver badges34 bronze badges

1 Comment

VBasic2008 Sep 30 at 7:33

Thx for the reminder: N(cond) = IF(cond,1,0).

user22566114 · Accepted Answer · 2025-10-02 15:25:03Z

2

Alternatively you can use MAP and LAMBDA to spill the result. G6# and F7# are TOROW(A2:A6) and TOCOL(A2:A6).

=LET(arr,LAMBDA(x,CHOOSE(x,SEQUENCE(ROWS(A2:A6)),SEQUENCE(,COLUMNS(G6#)))),
MAP(arr({1}),arr({2}),LAMBDA(a,b,
IFS(INDEX(F7#,a)=INDEX(G6#,,b),"-",
TRUE,N(XLOOKUP(INDEX(F7#,a),A2:A6,B2:B6)<XLOOKUP(INDEX(G6#,,b),A2:A6,B2:B6))))))

answered Oct 2 at 15:25

user22566114

5464 silver badges6 bronze badges

1 Comment

P.b Oct 3 at 21:50

Impressive how you make map spill 2D using CHOOSE that way.

Black cat · Accepted Answer · 2025-10-03 16:40:23Z

With theDATA tab Data Tools -> What-If-Analysis -> Data Table... command

The below screenshot shows the returned table

The data table is in A1:B5
Cell C9: =A1:B5
Cell E7: =TRANSPOSE(A1:B5)

Then select the range D8:I13 and click on the above described Data Table... command.
In the pop up window type or select cell D1 and D2 (two empty cells on the sheet) and click OK.
In cell D8: =IF(D2<D1,1,0)
This formula will generate the table with the result.

Row	A	B	C	D	E	F	G	H	I
1	A	1
2	B	5
3	C	3
4	D	2
5	E	4
6
7					A	B	C	D	E
8				0	1	5	3	2	4
9			A	1	0	1	1	1	1
10			B	5	0	0	0	0	0
11			C	3	0	1	0	0	1
12			D	2	0	1	1	0	1
13			E	4	0	1	0	0	0

Tom Sharpe · Accepted Answer · 2025-10-04 12:15:53Z

Not particularly short, but Makearray seemed the obvious one to try:

=LET(can,VSTACK("",DROP(A.:.A,1)), rank,B.:.B, MAKEARRAY(ROWS(can),ROWS(can),LAMBDA(r,c, IFS(r=1,INDEX(can,c),c=1,INDEX(can,r),r=c,"-", INDEX(rank,c)>INDEX(rank,r),1,TRUE,0))))

Candidate	Ranking		A	B	C	D	E
A	1	A	-	1	1	1	1
B	5	B	0	-	0	0	0
C	3	C	0	1	-	0	1
D	2	D	0	1	1	-	1
E	4	E	0	1	0	0	-

or indeed

=LET(can,VSTACK("",DROP(A.:.A,1)), rank,B.:.B, MAKEARRAY(ROWS(can),ROWS(can),LAMBDA(r,c, IFS(r=1,INDEX(can,c),c=1,INDEX(can,r),r=c,"-", TRUE,N(INDEX(rank,c)>INDEX(rank,r))))))

as P.b has suggested.

R vE · Accepted Answer · 2025-10-05 10:28:47Z

-1

With Candidates in A2:A6 and Rankings in B2:B6, why not simply create the 5x5 matrix in C1 with the simple formula below? Obviously, you can exchange the cell references with dynamic or named ranges, but there is no need for LET, LAMBDA, TOCOL, MAKEARRAY etc.

=VSTACK(
    TRANSPOSE(A2:A6),
    --(TRANSPOSE(B2:B6) < B2:B6)
)

edited Oct 5 at 10:28

answered Oct 5 at 10:27

R vE

805 bronze badges

Collectives™ on Stack Overflow

Using spreadsheet to make a pairwise comparison matrix

7 Answers 7

Row Beats Column

5 Comments

Comments

1 Comment

1 Comment

1 Comment

Comments

Comments

Hot Network Questions

Row	A	B	C	D	E	F	G	H	I
1	A	1
2	B	5
3	C	3
4	D	2
5	E	4
6
7					A	B	C	D	E
8				0	1	5	3	2	4
9			A	1	0	1	1	1	1
10			B	5	0	0	0	0	0
11			C	3	0	1	0	0	1
12			D	2	0	1	1	0	1
13			E	4	0	1	0	0	0

Row	A	B	C	D	E	F	G	H	I
1	A	1
2	B	5
3	C	3
4	D	2
5	E	4
6
7					A	B	C	D	E
8				0	1	5	3	2	4
9			A	1	0	1	1	1	1
10			B	5	0	0	0	0	0
11			C	3	0	1	0	0	1
12			D	2	0	1	1	0	1
13			E	4	0	1	0	0	0

Collectives™ on Stack Overflow

7 Answers 7

Row Beats Column

5 Comments

Comments

1 Comment

1 Comment

1 Comment

Comments

Comments

Related

Row	A	B	C	D	E	F	G	H	I
1	A	1
2	B	5
3	C	3
4	D	2
5	E	4
6
7					A	B	C	D	E
8				0	1	5	3	2	4
9			A	1	0	1	1	1	1
10			B	5	0	0	0	0	0
11			C	3	0	1	0	0	1
12			D	2	0	1	1	0	1
13			E	4	0	1	0	0	0