Forum Discussion
XLOOKUP -return more than one result
It has just dawned on me that simple sorting the alternatives by rank might be a better way of achieving the objective.
= SORTBY(Alternative, Rank) "or, if the rank is to be calculated," = SORTBY(Alternative, RANK(num, num, -1)) "where 'num' is the underlying sequence that was ranked."
There are two possible strategies. The first is to 'tie-break' your ranking to ensure each is unique. The other, as Detlef_Lewin suggests, is to use FILTER.
= MAP(seq, LAMBDA(k,
TEXTJOIN(",",, FILTER(Alternative, Rank=k, "none"))
))TEXTJOIN is needed to get around the 'nested arrays' limitation in the current Excel implementation of the calculation.
PeterBartholomew1 -Thanks, Peter. I'll try tie-breaking as I'm not familiar with FILTER.
In the longer term it is well worth getting familiar with the new 365 functionality. It is capable of replacing most of what you know and love about spreadsheets. A function to consider when tie-breaking ranking is to add a COUNTIFS. If everything is identical count how many instances have a smaller row number or ID.