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."
rather an exotic approach but might give more visual impressions about distribution of your results
=REPT(TRANSPOSE(Alternative);--(TRANSPOSE(Rank=SEQUENCE(;4))))Your use of REPT came as a bit of a surprise! Interestingly, it also worked without coercing TRUE/FALSE to 1/0.
An example of the 'tie-breaker' could be
= RANK(num, num, 1) + COUNTIFS(num, num, Alternative, "<"&Alternative)Because of the limitation of the xxxIFS family of functions the other array must be provided by a range reference but, other than that, pretty much any set of distinct values will do.