Return corresponding names in descending order based on next column values.

[alirezae90]

Hi everyone,

Column A1:A5 contains unique names/references
Column B1:B5 contains numbers

I want column C1:C5 to be those names but based on their corresponding values in descending order.

I don't want to do any filtering, a function on C1 to read values from B1:B5 and return corresponding names in descending order.

Any help is appreciated.

 

[mikerickson]

This CSE formula should do what you want

=INDEX(A:A, 1000*MOD(LARGE($B$1:$B$14+ROW($B$1:$B$14)/1000,ROW(A1)),1), 1)&""


Change the B1:B14 as needed, but leave the A:A alone. (And the A1)

 

[gaz_chops]

This CSE formula should do what you want

=INDEX(A:A, 1000*MOD(LARGE($B$1:$B$14+ROW($B$1:$B$14)/1000,ROW(A1)),1), 1)&""


Change the B1:B14 as needed, but leave the A:A alone. (And the A1)

Again I'm curious as to why we need an array formula, mine seems to do the same!

 

[Aladin Akyurek]

This appears to do the same as my suggestion, what am I missing?

Try the sample I used...

 

[gaz_chops]

Try the sample I used...

Ah, got it, thank you

 

[mikerickson]

Again I'm curious as to why we need an array formula, mine seems to do the same!

Potential duplicates in column B.
Your MATCH based approach will return one of those duplicated values twice. My array approach will return them both.

 

[gaz_chops]

Potential duplicates in column B.
Your MATCH based approach will return one of those duplicated values twice. My array approach will return them both.

Thanks Mike, I realised after Aladin posted.