Exceljet

Quick, clean, and to the point

Display sorted values with helper column

Excel formula: Display sorted values with helper column
Generic formula 
=INDEX(sata,MATCH(ROWS(exp_rng),sort,0))
Explanation 

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.

How this formula works

This formula replies on a helper column that already contains a sequential list of numbers to represent an established sort order.  The numbers in the helper column are independent from the operation of this formula. As long as the sequence is continuous, it can represent an ascending or descending sort, or even an arbitrary sort. In most cases, values will come from a formula.

At the core, this is a simple INDEX and MATCH formula, where INDEX retrieves a value based on a specified row number:

=INDEX(item,row)

The trick is that the row is calculated with the MATCH function based on values in the sort column:

MATCH(ROWS($D$5:$D5),sort,0)

The lookup value in match is generated with the ROWS function and an expanding reference. In row 5 of the worksheet, the range includes one cell and ROWS returns 1. In row 6, the range includes two cells and ROWS returns 2, and so on.

The array is the named range "sort" (D5:D11). At each row, MATCH locates the lookup value, and returns the position of that row number in the original data.

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

The value returned by MATCH feeds into the INDEX function as the row number, and INDEX returns the item at that position in the original data.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.