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."
I do agree that FILTER is the better fit for this situation than XLOOKUP.
This is an in interesting arrangement for XLOOKUP that will return multiple per lookup and spill a matrix. Though as you can see the results are not quite ideal and it only works with 2 returns per lookup.
=XLOOKUP(rank,Table1[Rank],Table1[Alternative],"none",,{1,-1})