for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

102 watchers

SPR May 2023 7:49am, 17 May 2023 40,534 posts	Quote Pin SPR I use 365 at work, so could use it. Question is if files go to others that don't.
SPR May 2023 8:08am, 17 May 2023 40,535 posts	Quote Pin SPR This seems a good read on Xlookup Vs Index Match. exceljet.net
run free May 2023 5:53pm, 17 May 2023 5,288 posts	Quote Pin run free XLOOKUP is awesome and love the spill
Pothunter May 2023 9:23pm, 17 May 2023 7,797 posts	Quote Pin Pothunter Had a play with xlookup and it seems it doesn’t like named ranges if they are on a separate sheet. You can reference the range in long form (‘sheet name’!A2:A100) but I’ve got very used to using names in vlookup and index match
GregP May 2023 6:50am, 18 May 2023 145,691 posts	Quote Pin GregP Is there something similar to =RANK() that produces unique values rather than assigning the same rank to identical numbers? [it’s for the DeathMatch Engine - this is important stuff]
EdJ May 2023 7:48am, 18 May 2023 693 posts	Quote Pin EdJ There are a couple of workarounds described here - myonlinetraininghub.com
GregP May 2023 8:27am, 18 May 2023 145,695 posts	Quote Pin GregP Yikes. Thanks. That's altogether less straightforward than I'd hoped.
GregP May 2023 8:33am, 18 May 2023 145,696 posts	Quote Pin GregP Is this simpler? My brain turned off when your article mentioned array formulas: https://www.extendoffice.com/documents/excel/4796-excel-rank-without-ties.html#:~:text=To%20rank%20list%20data%20without,down%20to%20apply%20this%20formula.
EdJ May 2023 8:54am, 18 May 2023 694 posts	Quote Pin EdJ I shared your reaction to the array formulas... The version you found looks much simpler but equally effective.
GregP May 2023 9:34am, 18 May 2023 145,697 posts	Quote Pin GregP I've stuck it into the DeathMatch Engine and it doesn't seem to have broken anything. Happy days.