Problem: In the previous example, you used the RANK function to find the relative rank order of four writers, as shown in Fig. 377. Now you want to use a formula to produce a sorted list of the writers in high-to-low sequence.
Strategy: In cells F28 through F31, enter the numbers 1 through 4. Use the VLOOKUP function to return the name in column G and the pages in column H. Here is how this function works.
VLOOKUP stands for vertical lookup. There are four parameters to the VLOOKUP function. In plain language, you are asking Excel to look for the value in F28 in the first column of F23:H26. When Excel finds an exact matching value, it returns the name in the second column of the lookup range.
The first parameter is the value that you are trying to match. In the case of cell G28, you would be looking for the value in F28. Write this as $F28 so that you can copy the formula to column H without rewriting that parameter.
The second parameter is the database range containing rows and columns of data. The key value that you are looking up must be in the first column of the range. In this case, it would be $F$23:$H$26. Note that you use dollar signs before both the column letters and row numbers in order to keep the database range absolute as you copy the formula.
The third parameter tells Excel the column from which you want to return the answer. For the name in column G, it is column 2 of the range F23:H26. For the page count in column H, it is column 3 of the range F23:H26.
The fourth parameter tells Excel if you will allow a close match. If your original data is not sorted, you are required to specify an exact match. For the fourth parameter, use TRUE for a close match and FALSE for an exact match.
Follow these steps:
-
As shown in Fig. 378, enter the following formula in G28: =VLOOK UP($F28,$F$23:$H$26,2,FALSE).
Advertisement -
Copy cell G28 to H28. The result in H28 will also be Josh, as shown in Fig. 379.
-
Edit the formula in the formula bar to change the third parameter from column 2 to column 3, as shown in Fig. 380.
Advertisement
The result in H28 will now contain the number of pages written by Josh, as shown in Fig. 381.
- Copy G28:H28 down to the next three rows. You will now have a
sorted list of the data, as shown in Fig. 382.
Additional Details: Your goal is to always enter one formula that you can copy to the entire data range. In this case, your formula in G28 could be copied to anywhere in column G, but when you copied it to column H, the third parameter had to be manually edited. You needed to plan ahead to use the proper combination of dollar signs in the references in order to ensure that three of the four parameters were correct when you copied the formula to column H.
If you find that you have only a few columns of data in an example like this, you can edit the third parameter manually. If you have many columns of data, this could get tedious. The =CELL(“Col”,G28) function would return a column number of cell G28. This would return a 7 for column G and an 8 for column H. You could have used the following formula in G28: =VLOOKUP($F28,$F$23:$H$26,CELL(“Col”,G28)–5,FALSE)
If you enter this formula in G28, you can copy it to all rows and columns of your results table.
Summary: After using a RANK function to assign rank values to a list, 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.
Functions Discussed: =VLOOKUP(); =CELL(); =RANK()
Cross Reference: Rank Scores
Images
