Excel VLOOKUP Week Sharks

Excel VLOOKUP Week Sharks

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.

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.

image

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.

vlookupshark01

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.

vlookupshark02

The next argument is table_array. For that, we select the table with the state names and attack numbers – cells A4:C23.

vlookupshark03

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.

vlookupshark04

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.

vlookupshark05

Now, if we choose a different state, such as Hawaii, from the drop down list, we see the Total Attacks for that state.

vlookupshark06

The VLOOKUP formula for Fatal Attacks is almost identical. The only change is entering 3 as the col_index_num, instead of 2.

vlookupshark07

Then, with two VLOOKUP formulas in place, we can see the Total Attacks and Fatal Attacks for any selected state.

vlookupshark08

Excel VLOOKUP Videos

I’m a longtime fan of VLOOKUP, despite some of its weaknesses, and have made several Excel VLOOKUP videos that show you how to use the function in different scenarios.

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.

30 Excel Functions in 30 Days

_______________

One thought on “Excel VLOOKUP Week Sharks”

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.