Who's afraid of the big bad vlookup?

Little Red Riding Hood is lost. And it's not in a dark, scary wood...oh no, it's in the lot of a Hollywood Studio. You see, what with all the rehashing of classic fairytales at the moment, the casting directors are getting confused and sending the actors to the wrong studios. What they need is a good vlookup formula to get themselves on track (yes, seriously)......

So imagine these were there current projects:

What this table shows, besides the fact that handsome princes are in demand and fairies and stepmothers have got a bad name, is that there is a fair amount of opportunity to get confused with all the various details.

If each casting director were sent a lovely little lookup tool as shown below, they could select their fairytale from the dropdown list, and the answers would automatically flow into place, along with the correct studio number.

Now before you fall into a 100 year sleep, let me explain VLOOKUPs as simply as I can. The official syntax of a vlookup is:

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup).
Yup. Hardly surprising it sends most people running for their fairy godmothers. Instead of fleeing, if you are using a VLOOKUP formula, try saying this in your head as you type it out. It may help you make sense of the formula:

=VLOOKUP(look for this, in here, bring back stuff from that column, false). So much easier than the official set of syntax, no?

Here is what the formula means, in plain english:

lookup_value - this is the cell reference of the item you want to search for in the data table. So for our example above, the lookup_value is the Fairytale name.

table_array - this is the table of data that you are using to search for all your answers.
NOTE: for vlookups to work, the first column in the table of data needs to be the column containing your lookup_values. In our example, that means the 'Fairytale' column must come first.

col_index_number - this will depend on what data you want to bring back. In our example, to bring back the Leading Lady, you insert a 2, as this info is contained in the 2nd column of data if you start counting columns (left to right) from the column that contains your lookup_values.  For Good Guys, use 3, for Bad Guys, use 4 etc.

range_lookup - This is always either TRUE, FALSE or you can leave it out, which is the same as typing in TRUE. To get an exact match on your lookup_value, type in FALSE. If it can't find an exact replica of the item you are searching for, you will get an #N/A. If you type in TRUE, it will look for the nearest match and bring back that data. This has its uses (see below) but in our example, you would always use FALSE. In fact, whenever you want 'exact match or nothing', use FALSE.

Some points on VLOOKUPS:
1) If you have duplicates in your first column, the vlookup will always bring back the data from the first instance. So check for duplicates if you're not getting the answer you expect.
2) If you are going to be copying the formula into a number of cells, make sure your table data is 'locked' into the formula. So select your table co-ordinates and press F4 to get the little $ signs.
3) Spaces matter in vlookups. If you download or import data from a reporting system, it is quite likely there may be a space at the end of your data. This will prevent your vlookup from working. Delete the space, et voila.
4) If you are not quite comfortable typing in the formula freehand, use the formula function box to help you the first couple of times. After that, you will probably find it much easier just to type it yourself.

So, back to the issue of TRUE or FALSE. Using TRUE is really handy if you want to look up a number that falls within a range of numbers. So if you were grading papers and wanted to give students an A-F grading based on their % marks, this is a handy tool to use.

Similarly, if we wanted to give our characters a 'goodness' rating, we could do the following (I've added the formula in at the bottom again for explanation):

This formula works by first checking for an exact match. If it can't find it, it will look for the next LOWEST number. So your table always needs to be in ascending order. In our example, Mother Gothel has not yet reformed her ways enough to even be classified as Misguided (40%), so she goes straight into the Evil & Twisted basket.

So, that is your basic VLOOKUP. These formulas are normally used for extracting data from one table into another, but can be used for just about anything. I've kept it simple for ease of explanation, but you can add in all sorts of other functions to make these formulas truly perform magic.

In our newsletter this week, I will be explaining how an HLOOKUP works...why not subscribe?

Happy Spreadsheeting!


--