Excel formula doesn't match Excel cell

Question

I have a game log that contains a running ELO score. I am attempting to search through this game log to retrieve the most recent ELO recorded for each player. I believe I have managed to do this successfully with the following formula.

=INDEX(SORT(VSTACK(IFERROR(HSTACK(XMATCH($A4, Games!$B:$B, 0, -1), INDEX(Games!$E:$E, XMATCH($A4, Games!$B:$B, 0, -1))), {0,1500}),
                   IFERROR(HSTACK(XMATCH($A4, Games!$F:$F, 0, -1), INDEX(Games!$I:$I, XMATCH($A4, Games!$F:$F, 0, -1))), {0,1500}),
                   IFERROR(HSTACK(XMATCH($A4, Games!$K:$K, 0, -1), INDEX(Games!$N:$N, XMATCH($A4, Games!$K:$K, 0, -1))), {0,1500}),
                   IFERROR(HSTACK(XMATCH($A4, Games!$O:$O, 0, -1), INDEX(Games!$R:$R, XMATCH($A4, Games!$O:$O, 0, -1))), {0,1500})), 1, -1), 1, 2)

The issue I'm now having is that a single cell in the spreadsheet is displaying a different value from that which is calculated by the formula (per the preview).

Are there known "gotchas" with any of these functions (INDEX, SORT, XMATCH, etc) and/or an alternative solution for traversing the game log and finding the ELO?

I did come up with an alternative formula, but it seems to run significantly slower and eventually creates an error message in Excel that it can't calculate if I add enough rows.

=XLOOKUP($A4, TOCOL(HSTACK(Games!$B:$B, Games!$F:$F, Games!$K:$K, Games!$O:$O)), TOCOL(HSTACK(Games!$E:$E, Games!$I:$I, Games!$N:$N, Games!$R:$R)), 1500, 0, -1)

Are there alternative strategies for solving this kind of problem and/or best practices I'm unaware of?

Example data:

Player A	Initial ELO	Final ELO	Player B	Initial ELO	Final ELO	Team A Score	Player C	Initial ELO	Final ELO	Player D	Initial ELO	Final ELO	Team B Score
George	1500	1516	John	1500	1516	11	Thomas	1500	1484	James	1500	1484	9
Andrew	1500	1475	Martin	1500	1475	0	William	1500	1525	Zachary	1500	1525	11
George	1516	1537	John	1516	1537	11	William	1525	1504	Zachary	1525	1504	4
Thomas	1484	1509	James	1484	1509	11	Andrew	1475	1450	Martin	1475	1450	0

Example result:

Player	ELO
George	1537
John	1537
James	1509
Thomas	1509
William	1504
Zachary	1504
Andrew	1450
Martin	1450

That looks like you're using an Excel table so you should be able to reference your data using something like =Table1[ELO] - changing Table1 to the name of your table. — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Jun 13 at 14:29
@DarrenBartrup-Cook I've added some sample data, I'm not sure how the table notation would help me retrieve the last row for each player? — Derrick Moeller
– Derrick Moeller, Commented Jun 13 at 15:37
It wouldn't help retrieve the last row for each play, but would mean you don't have to reference the whole column. Looking at the screenshot again though it doesn't seem like it's in an Excel table as there's no Table Design tab along the top. — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Jun 13 at 17:10
With Player A from your example data in A1 and George from your example result in A11 you could use =LET(dat,WRAPROWS(TOCOL(CHOOSECOLS(A2:N5,SEQUENCE(,6),SEQUENCE(,6,8))),3),XLOOKUP(A11:A18,CHOOSECOLS(dat,1),CHOOSECOLS(dat,3),,,-1)) — Spectral Instance
– Spectral Instance, Commented Jun 13 at 17:49
@SpectralInstance Most of those functions are new to me, so I'm still trying to understand how it works and I need to test to see if performance is better than my existing solutions. It does appear to return the correct result though. — Derrick Moeller
– Derrick Moeller, Commented Jun 13 at 18:36

Black cat · Accepted Answer · 2025-07-28 16:38:36Z

2

Another solution similar to the OP:

=LET(st,WRAPROWS(TOROW(HSTACK(A2:F5,H2:M5)),3),
uni,UNIQUE(CHOOSECOLS(st,1)),
res,HSTACK(uni,XLOOKUP(uni,CHOOSECOLS(st,1),CHOOSECOLS(st,3),,0,-1)),
res)

The first line arrange the columns without the TEAM data and then convert it to a 3 columns table.
The second row generates the unique names of the players.
The third row makes the LOOKUP for the unique players from the end of the table and join with the unique names.

answered Jul 28 at 16:38

Black cat

7,6358 gold badges69 silver badges99 bronze badges

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

Comments

JB-007 · Accepted Answer · 2025-07-25 02:35:04Z

1

=LET(a_,VSTACK(A1:C4,D1:F4,H1:J4,K1:M4),a1_,INDEX(a_,,1),a2_,INDEX(a_,,2),a3_,INDEX(a_,,3),z_,FILTER(a_,--ISNUMBER(MATCH(a1_&a2_,a1_&a3_,0))),z2_,FILTER(a_,1-ISNUMBER(MATCH(a1_,INDEX(z_,0,1),0))),z3_,IFERROR(VSTACK(z_,z2_),z_),SORTBY(CHOOSECOLS(z3_,1,3),TAKE(z3_,,-1),-1))

central 'filter': matching name&initial list to name&final list.... e.g. George.1516 (initial) matches George.1516 (final) - so final with 1516 is retained; but George.1500 (initial) has no corresponding match so that record dropped... all other bits of code essentially 'fluff' - e.g. single-player games, removing initial col so only name&final displays, and sorting descending order...
second filter (z2_) determines 'solo' games where individual only played once, and appends this to z_ (players who played more than 1 game). As a check - change Andrew (either one) to Andrew2. Both Andrew and Andrew2 now appear in final list..(correctly so in this case).
cannot have two different players with same name - so if there are 2 different players called John, use John01, John02, etc.
fails for 'palindromic' ELO patterns; e.g. if George's ELO 1500->1452->1500 then two final ELOs may be returned for him -- no way to discern which unless far more complicated function developed to check opponents' score (and if same happened with opponent then would require 'depth3' challengers' challengers' scores etc.)

edited Jul 25 at 2:35

answered Jul 25 at 2:29

JB-007

2,6441 gold badge9 silver badges23 bronze badges

1 Comment

Derrick Moeller Jul 28 at 12:39

Thanks for making an effort, however the 'palindromic' ELO pattern is definitely an issue. My current solution is basically as proposed by Spectral Instance in the comments.

Derrick Moeller · Accepted Answer · 2025-07-28 12:35:26Z

1

The solution that worked for us was largely influenced by Spectral Instance's solution in the comments.

Shown is the solution that works with the sample data.

=SORT(HSTACK(UNIQUE(TOCOL(CHOOSECOLS($A:.$N, 1, 4, 8, 11))), LET(dat, WRAPROWS(TOCOL(CHOOSECOLS($A:.$N, 1, 3, 4, 6, 8, 10, 11, 13)), 2), XLOOKUP(UNIQUE(TOCOL(CHOOSECOLS($A:.$N, 1, 4, 8, 11))), CHOOSECOLS(dat, 1), CHOOSECOLS(dat, 2), 1500, 0, -1))), 2, -1)

answered Jul 28 at 12:35

Derrick Moeller

5,0502 gold badges27 silver badges49 bronze badges

Collectives™ on Stack Overflow

Excel formula doesn't match Excel cell

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related