Display sorted values with the helper column
Sometimes it is required for us to retrieve to divide the value that is sorted whenever required. To do this with a helper column we can use the Excel INDEX, ROW and MATCH functions.
=INDEX(list, MATCH(ROWS(range),sort, 0))
How does the formula work?
- The formula works for a helper column which already has the sorted list of numbers in an order and these numbers are not dependent on the operation for this formula.
- The sort can be either an ascending or a descending one depending on the continuity of the sequence. The MATCH and INDEX functions always retrieve the value of a specific row number and the catch is that the row is estimated using a MATCH which II based on the sort column values.
- Therefore now, a lookup value is generated. Now at each of the rows, the MATCH function locates the value of a lookup and gives the position of that particular row-number. Since we are looking for an exact match, the third parameter is a ‘0’.
- The value returned by a MATCH function is an input to iNDEX and this function returns the ‘value ‘at that position.
Let us consider a list of the table containing different items its sales report. Let us find out the item Glasses and Hats from the provided list and therefore the result is as obtained when used the INDEX, MATCH AND ROW functions. To perform this operation let us view this stepwise.
- Opening finds the sorted values for the items as shown in the figure using the formula as specified in the formula bar of the excel worksheet.
Figure 1. The result obtained which displays the sorted values for the items.
Next step is to locate the items using the sorted values by using the ROWS, MATCH and INDEX functions as shown in the formula bar of the spreadsheet and the result is as obtained as shown in the figure which successfully displays the items ‘Glasses’ and ‘Hats’.
Figure 2. The result obtained which displays the sorted items when the ROWS, MATCH and INDEX functions used.