## Sort names by value

I have the following situation:

A1, B1, C1, D1, E1, F1

where

A1 = nick

b1 = 10

c1 = zack

d1 = 15

e1 - john

f1 = 13

what formula should i use to get them ordered counting the numbers but names still being asociated, like this:

a1 = zack

b1 = 15

c1 = john

d1 = 13

e1 = nick

f1 = 10

Formula in cell A3:

### Explaining formula

*Find n-th largest number in cell range and repeat every number*

ROUND(COLUMN(A1)*0.5,0)

becomes ROUND(1*0.5,0)

becomes ROUND(0.5,0)

and returns 1. Copy the formula to the next cell gives ROUND(COLUMN(B1)*0.5,0) and returns 1 again.

As you copy the formula to the left this sequence is created 1,1,2,2,3,3,4,4,5,5 and so on.

What happens if we combine the above with the LARGE function?

LARGE($A$1:$F$1,ROUND(COLUMN(A1)*0.5,0))

The LARGE function extracts the n-th largest number twice for every two cells. This allows us to return both the number and the corresponding name later on.

**Find position of the n-th largest number in cell range**

This formula is valid for cell A3, when you copy it to cell A4 the cell refs change. Check out blog post absolute and relative cell references and learn how to build smarter formulas.

MATCH(LARGE($A$1:$F$1,ROUND(COLUMN(A1)*0.5,0)),$A$1:$F$1,0)

becomes

MATCH(15,$A$1:$F$1,0)

and returns 4. Read more about MATCH function.

**Subtract one to get the corresponding name for every second cell.**

MATCH(LARGE($A$1:$F$1,ROUND(COLUMN(A1)*0.5,0)),$A$1:$F$1,0)-MOD(COLUMN(A1),2)

MOD(COLUMN(A1),2) returns 1,0,1,0,1, ... and so on as you copy the formula further to the right. This enables the formula to extract the name for every second cell.

More MOD examples: Learn how the MOD function works, Quotient, Mod and Int functions

**Return number or name**

=INDEX($A$1:$F$1,MATCH(LARGE($A$1:$F$1,ROUND(COLUMN(A1)*0.5,0)),$A$1:$F$1,0)-MOD(COLUMN(A1),2))

The INDEX function returns n-th largest number or corresponding name.

### Data vertically

This formula returns data vertically:

**Download excel *.xlsx**

### Category: Mod

Learn how the MOD function works

The Mod function returns the remainder after a number is divided by divisor. The Mod function is short for the Modulo […]Comments(8) Filed in category: Excel, Mod, Quotient

Comments(5) Filed in category: Excel, Frequency, Mod

Quotient, Mod and Int functions

You are about to send your company products to customers. You have boxes you can send the products in. You […]Comments(3) Filed in category: Excel, Mod

Create a repeating list of numbers from 1 to Nth value

Question: How do I create a repeating list of numbers from 1 to Nth value? In my case from 1 […]Comments(2) Filed in category: Excel, Mod

### Category: Sort values

Comments(81) Filed in category: Excel, Sort values

Sort dates within a date range using excel array formula

Question: I have a list of unsorted dates. I want to extract records between two dates and I want the […]Comments(23) Filed in category: Excel, Sort values

Extract unique distinct values from a filtered table (udf and array formula)

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]Comments(17) Filed in category: Excel, Sort values, Unique distinct values

Sort values in parallel (array formula)

Table of contents How to sort a table by Column 1 and then by Column 2 (array formula) How to […]Comments(16) Filed in category: Excel, Sort values

Lookup and return multiple values sorted in a custom order

Pat asks: Hi Oscar, Thanks for creating such a helpful website and I've a question if I would like to […]Comments(13) Filed in category: Excel, Sort values, Vlookup

### 7 Responses to “Sort names by value”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Elegant solution!

Excellent job Oscar, and not for the first time.

I like your site.

Beautiful formula,

you're not even an array.

Thank you all!

David, you are right. You don't need to enter the formula as an array formula. I was wrong. Thanks for pointing that out.

By the way, substitute the LARGE function with the SMALL function to sort values in ascending order.

[…] ← Previous post - […]

By adding a small value like COLUMN($A$1:$F$1)/10000 we can handle ties(not array formula entered in A3):

=INDEX($A$1:$F$1,MATCH(AGGREGATE(14,6, $A$1:$F$1+COLUMN($A$1:$F$1)/10000,ROUND(COLUMN(A1)*0.5,0)),INDEX($A$1:$F$1+COLUMN($A$1:$F$1)/10000,,),0)-MOD(COLUMN(A1),2))