excelexcel-formula

Excel formula doesn't match Excel cell

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?

Excel Screenshot

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
Solution

  • 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)