Excel Trick - Table & VLookup

I learned another trick with Excel that you can use for a variety of purposes. This example is using a grade distribution but you can use it for all sorts of data.

This tip requires you to have the data sorted from smallest to largest if you do not have it organized in this manner, this tip will not work.

And when you set up the VLookup function be sure to make sure when you copy and paste that you don't mess up the formula.

The formula you can put in a cell to see what grade it is uses the VLookup command and "true" or "1" to have it look for the closest value. If you use "false" or "0", this will not work.

In another cell put =vlookup(E1, $A$1:$B$5, 2,1). This looks into the second column to pull the value in this case Column B. The "1" indicates "true."

Another variation is to use this formula =vlookup(E1,A:B,2,1). This will look for the entire columns of A and B.

Next tip we will talk about Pivot Tables, I was amazed to hear that 80% of Excel Users have never used Pivot Tables.