Sort Data using INDEX and Other Functions

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to sort our data in multiple columns using INDEX and other functions.


Sample Data

The sample data below shows List of Countries, Country Code and Population. We have the same data in both sheet. Note, the data is officially formatted as an Excel Table

In the sample data, we have 39 countries only

Create Helper Column

In order to make our formula readable and neat, we are going to create an helper column in column E of Solution sheet. This is necessary to identify the original position of for instance, Sweden. Therefore,

  • In cell E2 of Solution sheet, execute the formula: =COUNTIF(Data[Country Code],”<=”&Data[@[Country Code]])
  • Copy the formula down using auto-fill to row 40.

From the caption below, we can tell that Sweden is going to be in the position 34 despite occupying the position 1 currently

Index & other Functions

Next, we need to in cell A2 deploy the row function minus 1 to give us 1 in cell A2, therefore,

  • In cell A2, execute the formula: ROW()-1
  • Copy down the formula

Next, we are going to deploy the XMATCH (although, MATCH function can perfectly do the same)

  • Press F2 to open the formula in cell A2 and carefully wrap the exisiting formula inside XMATCH. We have the following formula: =XMATCH(ROW()-1,$E$2:$E$40)
  • Click Enter and copy down the formula. See the caption below

From the above caption, the 20 in cell A2 means that top ranked country which is Australia in sitting in cell A21 of the Data sheet. If we don’t have header row in row 1 then Australia will be in row 20

  • Finally, we need to throw in the powerful INDEX Function  to return the list of Countries based on the predefined relative position. We have the below formula after factoring INDEX into the formula


  • Click Enter and copy down the formula

From the caption below, we can clearly see that we have Sorted the data alphabetically and task is completed

See you in the next tutorial

Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *