SOLVED

Mirroring Cells

%3CLINGO-SUB%20id%3D%22lingo-sub-2524413%22%20slang%3D%22en-US%22%3EMirroring%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2524413%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20each%20player's%20average%20ranking%20to%20show%20up%20underneath%20%22Positional%20Tiers%22%20in%20addition%20to%20Column%20O.%20The%20order%20of%20players%20will%20be%20changing%20a%20lot%2C%20so%20A2-O2%20could%20swap%20with%20A4-O4%2C%20etc.%20If%20I%20set%20Cell%20X3%20to%20%22%3D%20O21%22%2C%20making%20Patrick%20Mahomes's%20Average%20Ranking%20show%20up%20next%20to%20his%20positional%20tier%20as%20well%2C%20the%20value%20changes%20if%20he%20moves%20up%20or%20down%20any%20rows.%20Columns%20A-O%20will%20be%20sorted%20by%20the%20numeric%20values%20that%20will%20be%20in%20Column%20O.%20I%20would%20like%20to%20know%20how%20to%20set%20this%20X3%20cell%20to%20mirror%20whichever%20Column%20O%20cell%20is%20in%20the%20same%20row%20as%20Patrick%20Mahomes.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mcwinters19_0-1625681643244.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294099i07545B17695D4D25%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22mcwinters19_0-1625681643244.png%22%20alt%3D%22mcwinters19_0-1625681643244.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2524413%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2524486%22%20slang%3D%22en-US%22%3ERe%3A%20Mirroring%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2524486%22%20slang%3D%22en-US%22%3Esounds%20like%20you%20want%20XLOOKUP%20(or%20VLOOKUP%20or%20INDEX(MATCH())%20combo).%20So%20in%20X3%3A%3CBR%20%2F%3E%3DXLOOKUP(%24W%243%3A%24W%2421%2C%24A%242%3A%24A%24100%2C%24O%242%3A%24O%24100)%3CBR%20%2F%3Eyou%20may%20need%20to%20adjust%20the%20ranges%20and%20note%20the%20%24W%243%3A%24W%2421%20is%20a%20dynamic%20array%20that%20should%20fill%20the%20whole%20column%20down%20in%201%20formula%20instead%20of%20only%20using%20W3%20and%20then%20filling%20down.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2524491%22%20slang%3D%22en-US%22%3ERe%3A%20Mirroring%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2524491%22%20slang%3D%22en-US%22%3EThis%20would%20be%20a%20perfect%20situation%20for%20a%20lookup.%20If%20you%20have%20office%20365%2C%20try%20using%20XLOOKUP%2C%20something%20in%20X3%20would%20be%20%3DXLOOKUP(W3%2CA%3AA%2CO%3AO).%20This%20searches%20for%20whatever%20is%20next%20to%20it%2C%20finds%20it%20in%20column%20A%2C%20then%20returns%20whatever%20is%20in%20that%20same%20row%20in%20column%20O.%20Vlookup%20will%20be%20a%20similar%20function%2C%20should%20be%20easy%20to%20convert%20if%20you're%20running%20and%20older%20version.%3C%2FLINGO-BODY%3E
New 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 (New 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.