Retrieving the most recent game record in google sheets

Question

I have a google sheet that looks like this:

Player 1 name	Player 2 Name	Player 3 Name	Player 4 name	Player 1 Pre-Game ELO	Player 2 Pre-Game ELO	Player 3 Pre-Game ELO	Player 4 Pre-Game ELO	Player 1 Post-Game ELO	Player 2 Post-Game ELO	Player 3 Post-Game ELO	Player 4 Post-Game ELO
Dan	Stacy	Derek	Joel	1500	1500	1500	1500	1560	1440	1440	1440
Stacy	Dan	Donny	Ivanka	x	x	x	x

For the second row, to determine each player's pre-game ELO, I need to find each player's most recent Post-Game ELO. This is difficult, because I do not know if Dan's most recent game was as player 1, player 2, player 3 or player 4.

How can I perform this lookup?

I have tried VLookup, but that only outputs a value in some given column. I need different columns depending on which place the player was most recently in.

Answer 1

Welcome!

Too bad you didn't provide a link to the sample test data - it took a long time to create. I assumed that your table starts with column A and, accordingly, the "Post-Game ELO" columns are in I:L. If this is not the case, then you will need to change the formula, which we will construct together now step by step.

Let's put the name Dan in cell A6 and start building a formula for this player in cell E6.

First of all, let's make sure that the player for whom we are looking for a result is already in the table. If this is a beginner (like Donny and Ivanka in your example), then instead of a number, we indicate x.

To do this, use COUNTIF() to count how many times this name occurs in columns A:D above the current row.

=IF(COUNTIF($A$1:$D5,A6), <a number that we will find a little later>, "x")

Pay attention to the addressing of the search range - the beginning of the range is the absolute cell $A$1, and the end of the range is indicated by the absolute column $D and the relative row 5 - this is one row above the cell with our formula. Due to such addressing, we will be able to drag the finished formula across columns and rows without distorting the range.

Now let's find Dan's most recent result - find the last occurrence of Dan's name in the table, and when we know the desired cell in the range $A$1:$D5, take the value from the corresponding cell in the range $I$1:$L5. The main problem with your data layout is that the built-in search functions work on one column (row) and you have four.

So let's go for the trick, convert both of these ranges into one column using the FLATTEN() function. ...FLATTEN($I$1:$L5)...

For the range of names, we will make one more transformation - we will replace all names except Dan with 0. In other words, we will search in an array of the form {0;0;0;0;0;0;0;0;Dan;0;0;0;0;Dan;0;0;0;Dan;0;0}

...FLATTEN(IF($A$1:$D5=A6,A6,0)...

We have all the necessary data - we will use the INDEX(MATCH()) construction to get the result. There is a lot of information on the Internet about this method of searching for values, I will not repeat those texts (and videos).

=ArrayFormula(IF(COUNTIF($A$1:$D5,A6),INDEX(FLATTEN($I$1:$L5),MATCH(A6,FLATTEN(IF($A$1:$D5=A6,A6,0)),1)),"x"))

Expand the formula along columns E:H and down to the required number of rows

Answer 2

Use filter and query. Assume that the first row is 1, and the first column is A. Separate data into two groups:

• Player's Name: A2:D2
• Post-Game ELO: I2:L2

What I'm going to do is to FILTER the two groups of data, where the player's name equals the corresponding one (for example, for E3, corresponding to player A3). Then, use QUERY to select the second data.

However your data is all in the same row, FILTER will go on error telling you 'FILTER has mismatched range sizes'. We are going to use the TRANSPOSE formula to array the data into columns. You can type it somewhere to see the effect. Now, two group of data is:

• Player's Name: TRANSPOSE(A2:D2)
• Post-Game ELO: TRANSPOSE(I2:L2)

So, FILTER({TRANSPOSE(A2:D2),TRANSPOSE(I2:L2)},TRANSPOSE(A2:D2)=A1)

Please change the A1 to the corresponding name. The formula should give you the result: Stacy 1440. Now use the query formula to select the second column '1440'. =QUERY(FILTER({TRANSPOSE(A2:D2),TRANSPOSE(I2:L2)},TRANSPOSE(A2:D2)=A1),"select Col2") It should give you 1440 right now. You can use '$' in coordinate to lock that row/column number when you drag to fill cells.

=QUERY(FILTER({TRANSPOSE($A2:$D2),TRANSPOSE($I2:$L2)},TRANSPOSE($A2:$D2)=A3),"select Col2") Now you can drag it to the right (H column), then downward to duplicate the formula. Notice, if the player doesn't exist in the above row, this formula would not work. For example, on a player on row 4 had exist in row 2, but not row 3, the formula will not work. Please don't use ArrayFormula, this will not work.