Turbocharge your VLOOKUP with MATCH

Did you look at the VLOOKUP post and think, "Well, that's nice, but something about that col_index_number argument seems a bit crappy?" If so, you are DEFINITELY ready for the Jock curriculum. If this thought didn't cross your mind, that is perfectly reasonable; please read on.

Because Excel data tables are two dimensional (rows by columns), lookup functions are also two dimensional. VLOOKUP is dynamic in the vertical (rows) dimension - based on the lookup_value, the VLOOKUP will return different answers. As you change the value in the reference cell, the answer will change.

But what if you want to make it dynamic horizontally? The simple VLOOKUP is static horizontally, based on the col_index_number argument, which takes a simple number. As discussed, this number tells the function how many columns to the right the information you are seeking is located. What if instead of counting columns on your data table each time you write a VLOOKUP, you would rather let Excel do the work? This is where the MATCH function comes in handy.

The MATCH function finds the relative position of a value within a range. The reason it works so well with the VLOOKUP is that MATCH returns a simple number, which is exactly what VLOOKUP wants for the col_index_number argument. Here is what the function looks like:

MATCH(lookup_value, lookup_array, match_type)

For the lookup_value, you feed a reference for the value that you're looking to match, just as with VLOOKUP. The lookup_array is just like table_array in VLOOKUP, but instead of a large rectangle, the range will be only one row tall. The match_type argument is similar to the range_lookup in VLOOKUP, except instead of "false", we use the number zero for an exact match (the reason for this will make at least a little bit of sense in a later post when we deal with approximate matches, but for now, let's look for exact matches).

Tips:

1. The trickiest part of using MATCH with VLOOKUP is the lookup_array argument. The col_index_number will tell the VLOOKUP how many columns to the right to look relative to the first column. MATCH returns the number that represents how many columns to the right the lookup_value is, relative to the range you create. In order for this to work, the first column in both ranges must be the same. If your VLOOKUP table_array starts in column C, your MATCH range needs to start in column C, or else your results will be horrendously wrong.

2. I take it back, the trickiest part of using MATCH with VLOOKUP is getting your relative and absolute references working correctly. Because you are going to fill the function both down and across, things are going to get a bit messy. For review on using absolute and relative references, check out this post.

Let's kick that electricity analysis up a notch and take a look at some trends over time. Download VLOOKUP with MATCH here.