XLOOKUP -return more than one result

[Carl_Fromm]

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)?

[#2]

@Carl_Fromm 

Use FILTER().

[#3]

@Carl_Fromm 

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. 

[#4]

@Peter Bartholomew   -Thanks, Peter.  I'll try tie-breaking as I'm not familiar with FILTER. 

[#5]

@Carl_Fromm 

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.

[#6]

@Carl_Fromm

rather an exotic approach but might give more visual impressions about distribution of your results

=REPT(TRANSPOSE(Alternative);--(TRANSPOSE(Rank=SEQUENCE(;4))))

[#7]

@Balint79 

Your use of REPT came as a bit of a surprise!  Interestingly, it also worked without coercing TRUE/FALSE to 1/0.

@Carl_Fromm 

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. 

[#8]

@Carl_Fromm 

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})

[#9]

sql:
select `rank`,group_concat(alternative) from Sheet1 group by `rank`;

[#10 · Accepted solution]

@Carl_Fromm 

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."

[#11]

@Peter Bartholomew  Peter- thank you so much. You really helped me!