These tables (4 columns in Excel) are my input (here reduced to a minimal working example). The order of the values in column Val-A (here: largest, smallest, middle) is the order, in which Val-B should be sorted. That said, the second table should look like:

The order of the values in the input columns Val-A and Val-B as well as the IDs in ID-A and ID-B are arbitrary. It is also important to note that the values in Val-A and Val-B are never equal, i.e. none of the values in Val-A will be a value in Val-B.

How can I achieve this in Excel?

CodePudding user response：

I'm suggesting you rank the values in the first table, then match the ranks in the second table to those ranks to get the sort order that you need.

Using ranges:

=SORTBY(D2:E4,XLOOKUP(RANK(E2:E4,E2:E4),RANK(B2:B4,B2:B4),SEQUENCE(3)))

or using structured references:

=SORTBY(Table2,XLOOKUP(RANK(Table2[Val-B],Table2[Val-B]),RANK(Table1[Val-A],Table1[Val-A]),SEQUENCE(ROWS(Table2))))

EDIT

Case where second table has more rows than first.

A bit pushed for time on this so this isn't fully checked. I couldn't get Rank to work with filter, although the documentation suggestions that it should work, but it's OK with index. To avoid a lot of repetition, I've put it in a Let statement like this:

=LET(FilValB,INDEX(TableB[Val-B],1):INDEX(TableB[Val-B],ROWS(TableA)),
    FilTableB,INDEX(TableB,1,1):INDEX(TableB[Val-B],ROWS(TableA)),
    SORTBY(FilTableB,XLOOKUP(RANK(FilValB,FilValB),RANK(TableA[Val-A],TableA[Val-A]),SEQUENCE(ROWS(FilTableB)))))