Please Help Excel Rank Dilemma

As I said, my approach was "one way." One of the delightful things about Excel is that there are always several different ways to get from point A (the problem) to B (the solution). Patrick2788​ has given one that comes at the problem a different way, leaving the original array as it was in your introductory request, and producing the final column with the correct ranking.

I chose to rearrange the original array in sorted order.

Which result is "better" depends, as is often the case, on what you will be doing with that result, that output.

I may need to see more sample data and outcomes but this matches your desired return:

=LET(
    rank, SORTBY(name, Score, -1, back_6, -1, back_3, -1, back_1, -1),
    desired, XMATCH(name, rank),
    desired
)

Here's one way: The SORTBY function will sort the entire table, listing the names (and remaining columns) in the desired order. (By the way, you didn't specify that the rankings are by low score, not high; that's a key part of the definition of the problem; you perhaps took it for granted),

=SORTBY(A4:D10,B4:B10,-1,C4:C10,-1,D4:D10,-1)

Here's what it looks like (absent headers)

See attached for the working example.