Excel Functions can greatly manipulate data and they can even replace Pivot tables in some respects. Pivot tables are great to use but not everyone likes to use them. I am going to show you how to use Excel functions to create a new table from your current table and have it dynamically sorted.
We will start off by showing you the data.
We have a list of people, scores and age. We want to create another table and have it dynamically sort descending the data so it changes as your main table data changes.
So first we will create the new table and start off by pulling in the largest score using the LARGE function.
We used this formula: =LARGE($B$2:$B$11,1)
This checks the cells from B2 to B11 and pulls the largest number. Then we need to copy this down and change the last part of the formula. That number tells us what largest number we want. If we put a 2, it pulls the second largest number.
Now we have the scores dynamically being sorted. We just need to pull in the other data. So first let’s pull in Age by using a VLOOKUP.
We use this formula to get age: =VLOOKUP(G2,B:C,2,FALSE)
What this does is look for G2, which is the score in the range of scores and pull in the second column which is the age. (Note, for this to work, no scores can be the exact same since VLOOKUP pulls the first instance it finds)
So now we have the score and we can copy it down.
The last part of this is to get the name. But VLOOKUP won’t work because we are going from right to left. So we need to do an index match.
We used the following formula: =INDEX($A$2:$A$11,MATCH(G2,$B$2:$B$11,0),1)
What this does is INDEX will pull in a value from A2 to A11. That is the name we want.
The MATCH part tells us which number of the cell to pull in. So the MATCH in this instance looks up G2, which is 99.0 in the range of B2 to B11. It sees that it is the 9th cell down, so it returns a 9.
Now the INDEX function is looking in A2 to A11 and it goes down 9 cells, starting at A2. So it returns the value in cell A10 which is Tina.
Now once we copy this formual down (Make sure you anchor the ranges), you get:
So this new table is good to go. Change anything in the original table and you will see your new table dynamically sort!
If you would like more information on our program Master Excel Functions, which is a 114 page ebook and 4.5 hours of video, then go to :