Display sorted values with helper column

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.

Generic Formula

=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.

Example

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.

“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!”
- - Chris T, in California

Leave a Comment

This comment form is under antispam protection

This comment form is under antispam protection

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Trusted by people who work at

Plans & Pricing

I’ll use as many sessions as I want per month

$29.99

/month

Unlimited Sessions

Best offer

I want the unlimited sessions for 3 months AND save money

$74.99

/ 3 months

Unlimited Sessions for 3 months

Save $15 when you buy a subscription for 3 months

I want the unlimited sessions subscription AND save over a hundred dollars