## Return row reference of largest to smallest

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.

### Category: Excel

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.Comments(445) Filed in category: Excel, VLOOKUP and return multiple values

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 […]Comments(249) Filed in category: Concatenate, Excel, Textjoin

Comments(161) Filed in category: Charts, Excel, Interactive

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

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