SOLVED

Mirroring Cells

Copper Contributor

I want each player's average ranking to show up underneath "Positional Tiers" in addition to Column O. The order of players will be changing a lot, so A2-O2 could swap with A4-O4, etc. If I set Cell X3 to "= O21", making Patrick Mahomes's Average Ranking show up next to his positional tier as well, the value changes if he moves up or down any rows. Columns A-O will be sorted by the numeric values that will be in Column O. I would like to know how to set this X3 cell to mirror whichever Column O cell is in the same row as Patrick Mahomes.

mcwinters19_0-1625681643244.png

 

2 Replies
sounds like you want XLOOKUP (or VLOOKUP or INDEX(MATCH()) combo). So in X3:
=XLOOKUP($W$3:$W$21,$A$2:$A$100,$O$2:$O$100)
you may need to adjust the ranges and note the $W$3:$W$21 is a dynamic array that should fill the whole column down in 1 formula instead of only using W3 and then filling down.
best response confirmed by mcwinters19 (Copper Contributor)
Solution
This would be a perfect situation for a lookup. If you have office 365, try using XLOOKUP, something in X3 would be =XLOOKUP(W3,A:A,O:O). This searches for whatever is next to it, finds it in column A, then returns whatever is in that same row in column O. Vlookup will be a similar function, should be easy to convert if you're running and older version.
1 best response

Accepted Solutions
best response confirmed by mcwinters19 (Copper Contributor)
Solution
This would be a perfect situation for a lookup. If you have office 365, try using XLOOKUP, something in X3 would be =XLOOKUP(W3,A:A,O:O). This searches for whatever is next to it, finds it in column A, then returns whatever is in that same row in column O. Vlookup will be a similar function, should be easy to convert if you're running and older version.

View solution in original post