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.
Video
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
=INDEX(Data[Countries],XMATCH(ROW()-1,$E$2:$E$40))
- 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