Forum Discussion
Solved
XLOOKUP -return more than one result
Table 1 (master table) below lists various alternatives A1, A2... along with their ranks. As seen, A1 and A3 both have the same rank (#1). I'm using XLOOKUP to produce a derivative Table 2 where alte...
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."
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.