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

return-row-reference-of-largest-to-smallest1Column 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

return-row-reference-of-largest-to-smallest21

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.