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. At the core, this is a simple INDEX and MATCH formula, where INDEX retrieves a value based on a specified row number: The trick is that the row is calculated with the MATCH function based on values in the sort column: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
