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.

 

[gaz_chops]

Welcome to Mr Excel,

Try

=INDEX($A$1:$A$5,MATCH(ROW(A1),$B$1:$B$5,0))

[TABLE="width: 261"]
<colgroup><col width="87" span="3" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="width: 87"]fred[/TD]
[TD="width: 87, align: right"]4[/TD]
[TD="width: 87"]dave[/TD]
[/TR]
[TR]
[TD]dave[/TD]
[TD="align: right"]1[/TD]
[TD]pete[/TD]
[/TR]
[TR]
[TD]jon[/TD]
[TD="align: right"]5[/TD]
[TD]dan[/TD]
[/TR]
[TR]
[TD]pete[/TD]
[TD="align: right"]2[/TD]
[TD]fred[/TD]
[/TR]
[TR]
[TD]dan[/TD]
[TD="align: right"]3[/TD]
[TD]jon[/TD]
[/TR]
</tbody>[/TABLE]

 

[alirezae90]

Welcome to Mr Excel,

Try

=INDEX($A$1:$A$5,MATCH(ROW(A1),$B$1:$B$5,0))

[TABLE="width: 261"]
<tbody>[TR]
[TD="width: 87"]fred[/TD]
[TD="width: 87, align: right"]4[/TD]
[TD="width: 87"]dave[/TD]
[/TR]
[TR]
[TD]dave[/TD]
[TD="align: right"]1[/TD]
[TD]pete[/TD]
[/TR]
[TR]
[TD]jon[/TD]
[TD="align: right"]5[/TD]
[TD]dan[/TD]
[/TR]
[TR]
[TD]pete[/TD]
[TD="align: right"]2[/TD]
[TD]fred[/TD]
[/TR]
[TR]
[TD]dan[/TD]
[TD="align: right"]3[/TD]
[TD]jon[/TD]
[/TR]
</tbody>[/TABLE]

Thanks.

In you example I want Jon to come first (descending order) and also this function did not work when I tried it.

 

[gaz_chops]

Misread the question!

Try

=INDEX($A$1:$A$5,MATCH(LARGE($B$1:$B$5,ROW(A1)),$B$1:$B$5,0))

[TABLE="width: 261"]
<colgroup><col width="87" span="3" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="width: 87"]fred[/TD]
[TD="width: 87, align: right"]47[/TD]
[TD="width: 87"]jon[/TD]
[/TR]
[TR]
[TD]dave[/TD]
[TD="align: right"]11[/TD]
[TD]fred[/TD]
[/TR]
[TR]
[TD]jon[/TD]
[TD="align: right"]58[/TD]
[TD]dan[/TD]
[/TR]
[TR]
[TD]pete[/TD]
[TD="align: right"]21[/TD]
[TD]pete[/TD]
[/TR]
[TR]
[TD]dan[/TD]
[TD="align: right"]37[/TD]
[TD]dave[/TD]
[/TR]
</tbody>[/TABLE]

 

[alirezae90]

Misread the question!

Try

=INDEX($A$1:$A$5,MATCH(LARGE($B$1:$B$5,ROW(A1)),$B$1:$B$5,0))

[TABLE="width: 261"]
<tbody>[TR]
[TD="width: 87"]fred[/TD]
[TD="width: 87, align: right"]47[/TD]
[TD="width: 87"]jon[/TD]
[/TR]
[TR]
[TD]dave[/TD]
[TD="align: right"]11[/TD]
[TD]fred[/TD]
[/TR]
[TR]
[TD]jon[/TD]
[TD="align: right"]58[/TD]
[TD]dan[/TD]
[/TR]
[TR]
[TD]pete[/TD]
[TD="align: right"]21[/TD]
[TD]pete[/TD]
[/TR]
[TR]
[TD]dan[/TD]
[TD="align: right"]37[/TD]
[TD]dave[/TD]
[/TR]
</tbody>[/TABLE]

Thanks again.

The issue here is that my actual cells don't start from A1 or B1 they start from A4 etc....modifying the function to =INDEX($H$5:$H$9,MATCH(LARGE($I$5:$I$9,ROW(H5)),$I$5:$I$9,0)) returns #NUM !

Any ideas as to why?

 

[Aladin Akyurek]

Book1
	A	B	C
1	jon	2	jack
2	nadia	6	nadia
3	jack	8	linda
4	vino	5	vino
5	linda	6	jon
Sheet1

In C1 control+shift+enter, not just enter, and copy down:

=IF(ROWS($C$1:C1)<=COUNTIFS($A$1:$A$5,"?*"),INDEX($A$1:$A$5,SMALL(IF($B$1:$B$5=LARGE($B$1:$B$5,ROWS($C$1:C1)),ROW($B$1:$B$5)-ROW($B$1)+1),SUM(IF(LARGE($B$1:$B$5,ROW($B$1:B1)-ROW($B$1)+1)=LARGE($B$1:$B$5,ROWS($C$1:C1)),1)))),"")

 

[alirezae90]

Thanks for that.

But do you know how I could use the function in this link:

https://www.get-digital-help.com/2013/12/13/lookup-and-return-multiple-values-sorted-in-a-custom-order/

to do an descending rather than ascending? As I also have one criterion too.

=INDEX($D$3:$D$14, MATCH(SMALL(IF($G$2=$B$3:$B$14, $C$3:$C$14, ""), ROW(A1)), IF($G$2=$B$3:$B$14, $C$3:$C$14, ""),0))

 

[gaz_chops]

Thanks again.

The issue here is that my actual cells don't start from A1 or B1 they start from A4 etc....modifying the function to =INDEX($H$5:$H$9,MATCH(LARGE($I$5:$I$9,ROW(H5)),$I$5:$I$9,0)) returns #NUM !

Any ideas as to why?

Try

=INDEX($H$5:$H$9,MATCH(LARGE($I$5:$I$9,ROW(A1)),$I$5:$I$9,0))

Row(A1) tells it to return the 1st largest thins copied down, 2nd 3rd etc

 

[gaz_chops]

To change ascending to descending change Small to Large.

 

[gaz_chops]

	A	B	C
jon	jack
nadia	nadia
jack	linda
vino	vino
linda	jon

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]6[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]8[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]6[/TD]

</tbody>

Sheet1​

In C1 control+shift+enter, not just enter, and copy down:

=IF(ROWS($C$1:C1)<=COUNTIFS($A$1:$A$5,"?*"),INDEX($A$1:$A$5,SMALL(IF($B$1:$B$5=LARGE($B$1:$B$5,ROWS($C$1:C1)),ROW($B$1:$B$5)-ROW($B$1)+1),SUM(IF(LARGE($B$1:$B$5,ROW($B$1:B1)-ROW($B$1)+1)=LARGE($B$1:$B$5,ROWS($C$1:C1)),1)))),"")

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