One Must-Know Excel Function to Save Hours of Time

The Vlookup

Have you ever had to compare two lists of email addresses? How about flag a certain set of products out of a large transaction list? Or maybe mash up a few lists of ugly-looking customer IDs? This function will allow you to do all of it!

Lets take the example of two lists of customer IDs that you need to mash up to see which customers are members of both lists:

Vlookup 1

Rather than reading through and trying to spot the matching pairs, we will employ the powers of the Vlookup to show us where the matches are. This will produce the following result, where the matches are called out:

Lets have a look at how to set this up:

Vlookup2-1

Notice that there are only 4 elements in our unassuming Vlookup:

  • The first item is the cell value we want Vlookup to check for in the lookup-list (in this case, A2)
  • The second item is the lookup-list Vlookup will use to check for the first value you specified (in this case, D:D)
  • The third item tells Vlookup which value from the look-up list to return. Here we specify column 1 of the list (because, after all, there is only one column)
  • The forth item you should almost always enter “False”. This tells Vlookup to match values exactly.

Not so bad. Now just double click the little black box in the lower right corner of the cell to fill in all the cells below:

Vlookup2-2

Viola! You have found the matches! Now all that’s left is to distill down that big ugly list to the matches you care about. Drop a filter on it:

Vlookup5

And de-select that uncouth #N/A:

Vlookup6

BOOM! There are your customers who attended your webinar and are subscribed to your blog.

Vlookup7

Congratulations, you are well on your way to saving yourself hours of time. Before we move on to the next 2 functions, however, let’s take a look at one more awesome capability of our friend the Vlookup.

Remember that we mentioned you could return a different value from the lookup-list? Let’s see how that works – we will modify what our starting example looked like:

Vlookup8

Notice that our lookup-list now has two columns – the customer ID and the engagement that they showed during the webinar. What if we want to know which customers are members of both lists, AND now how engaged they were on the webinar? We can do that. Let’s modify our Vlookup:

VlookupFormula2-New

Notice two changes to our first Vlookup:

  • We’ve expanded our lookup-list to contain both columns instead of just one (D:E)
  • We’ve specified Vlookup to return the second column of the look-up table with the number 2

That’s it, we’re done. Just fill the formula down and you’ll have your matches showing webinar engagement.

Vlookup10

Excellent! Our advanced implementation of Vlookup was a success.

Leave a Reply