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 alternatives are listed in rows according to their rank. As seen in cell E3, XLOOKUP produces only one result (A1) rather than two (A1 and A3) under rank=1. Is there a way to make XLOOKUP return multiple results, or do I have to use some other function(s)?
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."
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 Peter- thank you so much. You really helped me!
- sql:
select `rank`,group_concat(alternative) from Sheet1 group by `rank`; 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})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.
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.
