Most valuable professional
  • Excel Book Excerpt

Excel Sorting with a Formula

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Sorting with a Formula

Problem: In “Rank Scores,” I learned how to use the RANK function to find the relative rank order of four writers. Now I want to use a formula to produce a sorted list of the writers in high-to-low sequence.

Strategy: In cells A8:A11, you enter the ranks 1 through 4. Then you use the VLOOKUP function to return the name in column B and the pages in column C.

LE10000329.jpg 

Figure 349 The table in rows 8:11 sorts the original data.

1. Set up a new table with numbers 1 through 4.

2. The formula in B8:B11 is =VLOOKUP($A8,$A$2:$C$5,2,FALSE).

3. The formula in C8:C11 is =VLOOKUP($A8,$A$2:$C$5,3,FALSE).

After using a RANK function to assign rank values to a list, you can use a second table with the numbers 1 through n and a series of VLOOKUP formulas in order to return a sorted list of the data.

<-Previous Topic                    Next Topic->

For more resources for Microsoft Excel: