Today I'm going to show you how to use the OFFSET function combined with Index and Match to find the values of diagonal cells in a table using my Super Bowl squares template as example.
Use the OFFSET function to find the value of a cell to the upper left, lower left, upper right, or bottom right of another cell. Let's say my name is in cell B2, the reference cell. To go up a row use -1, to go down a row use 1. Use -1 to go to a column to the left, +1 to go to a column to the right.
Upper left: =OFFSET(B2,-1,-1)
Upper right: =OFFSET(B2,-1,1)
Lower left: =OFFSET(B2, 1,-1)
Lower right: =OFFSET(B2, 1,1)
Now this is useful if you know the exact location of the reference cell, but what if that reference cell is always moving? That's where INDEX and MATCH come into play. A perfect example is taken from my Super Bowl Squares spreadsheet template. Watch the video below to see how I can get the values of the diagonal cells when the reference cell can change once I hit the randomize numbers button:
Use the OFFSET function to find the value of a cell to the upper left, lower left, upper right, or bottom right of another cell. Let's say my name is in cell B2, the reference cell. To go up a row use -1, to go down a row use 1. Use -1 to go to a column to the left, +1 to go to a column to the right.
Upper left: =OFFSET(B2,-1,-1)
Upper right: =OFFSET(B2,-1,1)
Lower left: =OFFSET(B2, 1,-1)
Lower right: =OFFSET(B2, 1,1)
Now this is useful if you know the exact location of the reference cell, but what if that reference cell is always moving? That's where INDEX and MATCH come into play. A perfect example is taken from my Super Bowl Squares spreadsheet template. Watch the video below to see how I can get the values of the diagonal cells when the reference cell can change once I hit the randomize numbers button:
What do you think of that? Would you like to see more examples like this, taken straight out of a template? Let me know in the comments below.
How do I change the name of the team in the spreadsheet "Pick your own Squares" when I click on the cells it just showes ='Printable Squares'!C3:L3. I want to change the team name. Please advise.
ReplyDeleteThanks,
Mark