Could things possibly be more exciting? I don't think so! Two major Excel events are happening, and you won't want to miss either one.
- It's Excel VLOOKUP week, as announced on the Microsoft Excel team's website. Chandoo had a VLOOKUP Week in November 2010, so I guess it only comes around every 18 months or so – don't miss it.
- Today, John Walkenbach is having a huge sale on his Excel Power Utility Pack (PUP) add-in. It's a great investment, even at full price, and I have reviewed the PUP add-in, and love using it. Check John's Spreadsheet Page Blog, for a link to the sales page. The sale starts at 11 AM Eastern time today, and lasts 24 hours only.
VLOOKUP Shark Attacks
They're using a shark logo for VLOOKUP week, because VLOOKUP "strikes terror into the hearts of Excel beginners". Well, we're big fans of sharks, here at Contextures, and like any excuse to celebrate. But if you think sharks are scary, you should definitely stay away from the VLOOKUP Vampires!
One year, during the Discovery Channel's Shark Week, we used the FLOOR function to analyze shark attack data for several states in the USA.
With Excel VLOOKUP, we could use the same table to pull the results for each state. In this example, I used Data Validation to create a drop down list of the states.
Then, in the next column, I created a VLOOKUP formula to find the Total Attacks for the selected state. The first argument in the VLOOKUP function is "lookup_value". We want the result for the selected state, which is in cell F4.
The next argument is table_array. For that, we select the table with the state names and attack numbers – cells A4:C23.
The lookup table has 3 columns, and the Total Attacks are in column 2 of that table. For the third argument, col_index_num, we'll enter 2 – the column where the Total Attack data is stored.
The final argument is range_lookup. In some formulas, we want an approximate value – like finding a student's letter grade, based on their percentage score. In this example, we want an Exact Match – the exact number of attacks for the selected state.
Now, if we choose a different state, such as Hawaii, from the drop down list, we see the Total Attacks for that state.
The VLOOKUP formula for Fatal Attacks is almost identical. The only change is entering 3 as the col_index_num, instead of 2.
Then, with two VLOOKUP formulas in place, we can see the Total Attacks and Fatal Attacks for any selected state.
Excel VLOOKUP Videos
For example, here's a video that shows you how to use the Excel MATCH function within a VLOOKUP formula, to make it more flexible.
Here's an example of using the approximate match in VLOOKUP, to convert student percentage grades to letter grades.
The next video show you how to use VLOOKUP to change a student's numeric score into a letter grade, by using the Approximate Match option.
And if you're having trouble with a VLOOKUP formula, this troubleshooting video might help.
You can watch more of my Excel VLOOKUP videos, on the Contextures website.
VLOOKUP and More Excel Functions
For examples of VLOOKUP, and 29 other Excel functions, check out my ebook kit – 30 Excel Functions in 30 Days.