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.