Display sorted values with helper column

To retrieve and display values sorted with a helper column, you can use an INDEX and MATCH formula, with a little help from the ROWS function. In the example shown, the formula in F5 is: =INDEX(sales,MATCH(ROWS($D$5:$D5),sort,0)) which displays first item, based on the index provided in the helper column. The same approach is used to display associated sales in column G. For convenience, the worksheet contains the following named ranges: item = B5:B11, sales = C5:C11, sort = D5:D11. In Excel 2021 and later, a better approach is to use the FILTER function. See this page for details.

April 14, 2025 · 1 min · 106 words · Michelle Hill

Table of Contents

The numbers in the helper column are independent of the operation of this formula.

In most cases,values will come from a formula.

In row 5 of the worksheet, the range includes one cell and ROWS returns 1.

Excel formula: Basic numeric sort formula

In row 6, the range includes two cells and ROWS returns 2, and so on.

The array is the named range “sort” (D5:D11).

Since we want an exact match, the third argument, match jot down, is supplied as zero.

Excel formula: Sort numbers ascending or descending

you might use INDEX to retrieve individual values, or entire rows and columns.

MATCH supports approximate and exact matching, andwildcards(* ?)

How to look things up with INDEX

Excel formula: Sort and extract unique values

Excel INDEX function

Excel MATCH function

Excel ROWS function