2

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.

2 Answers 2

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

2
  • This doesn't appear to work unfortunately. I can't assume how many rows there are to search, I don't know that there are only 5 rows to search which is what is assumed in this answer. Commented Sep 28, 2022 at 21:25
  • @DanielPaczuskiBak One of us made a mistake: either I built the formula, or you read the description. The five rows are only used as an example since we are building the formula for Dan in the sixth row. As the formula stretches, this number will increase. For example, for Dan in row 138 would be searched 137 rows above.
    – JohnSUN
    Commented Sep 29, 2022 at 7:57
1

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.

1
  • I don't want to just look at row 2, I want to search for all of the player's previous games. Not just at a specific row. So this isn't a working answer. There are many players, and only 4 per row. The previous game for a player could be hundreds of games ago. Commented Sep 28, 2022 at 21:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.