## Return row reference of largest to smallest

*Article updated on July 19, 2017*

Someone googled "return row reference of largest to smallest" and landed on my website. The question here is "largest to smallest" of what? Text or numbers? Well, I will cover both in this article.

**Return row reference of largest to smallest numbers
**

Column A contains random numbers. Column B contains row references to a sorted list. Se picture to the right.

Formula in B2:B6:

=MATCH(LARGE($A$2:$A$6, ROW()-1), $A$2:$A$6, 0)+1 + Ctrl + Shift + Enter

Return row reference of smallest to largest numbers?

=MATCH(SMALL($A$2:$A$6, ROW()-1), $A$2:$A$6, 0)+1 + Ctrl + Shift + Enter

Download excel sample file for this tutorial.

return-row-reference-of-largest-to-smallest

(Excel 97-2003 Workbook *.xls)

**Return row reference of a alphabetical list from Z to A
**

Column A contains random alphabetical text. Column B contains row references to a sorted list. Se picture to the right.

Formula in B2:B6:

=MATCH(LARGE(COUNTIF($A$2:$A$6, "<"&$A$2:$A$6), ROW()-1), COUNTIF($A$2:$A$6, "<"&$A$2:$A$6), 0)+1 + Ctrl + Shift + Enter

Return row reference of a alphabetical list from A to Z?

=MATCH(SMALL(COUNTIF($A$2:$A$6, "<"&$A$2:$A$6), ROW()-1), COUNTIF($A$2:$A$6, "<"&$A$2:$A$6), 0)+1 + Ctrl + Shift + Enter

Download excel sample file for this tutorial.

return-row-reference-of-largest-to-smallest

(Excel 97-2003 Workbook *.xls)

**Functions in this article:**

**ROW(**reference**)** returns the rownumber of a reference

**MATCH(**lookup_value;lookup_array; [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**LARGE(**array,k**)** returns the k-th largest row number in this data set.

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### 6 Responses to “Return row reference of largest to smallest”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**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

Hi :)

Thx...but what if there are more numbers there are alike and you want to know the row number of them?

If I try this code I only get the row number of the first number not the next numbers there have the same value (if I want to make a top 5 solution :))

NiH,

Download excel *.xlsx file:

NiH.xlsx

Oscar's formula in response the NiH's question (how to get row references from largest to smallest when list of numbers contains duplicates) appears to have a problem - it errors out with #NUM! when it gets to the first duplicate value.

Never mind my earlier comment - mistyped the formula!

Did we end up with the correct solution as this is what i need. Cheers Peter?

Is this correct please

Peter,

What are you looking for? Sorry, I don´t understand?