VLOOKUP is one of Excel’s most useful functions. This course provides an introduction to the VLOOKUP function as well as practical tips for how to use this function to improve the integrity of your spreadsheets, streamline productivity, and decrease accounting errors. We cover the nuances of VLOOKUP, how to counteract missing data with the IFERROR and IFNA functions, and ways to turbo-charge VLOOKUP by incorporating the MATCH function.

This course enables you to:

- Improve the integrity of spreadsheets with Excel's VLOOKUP function
- Recognize what causes look-up errors, and see how to overcome such situations
- Understand the new IFNA function available to Excel 2013 users
- Uncover the limitations of VLOOKUP, along with alternatives
- Perform dual lookups across columns and down rows to cross-reference the data you need

### Intro Video Transcript

Microsoft Excel Efficiency: VLOOKUP Function By David Ringstrom Hello, I am David Ringstrom, a CPA from Atlanta. Welcome to this Illumeo Presentation of Excel Efficiency VLOOKUP. In this presentation I will lead off with an introduction to the Vlookup Function, and then I’ll demonstrate the number of ways that Vlookup can go array and ways to counter those varies situations and deal with varies nuances in Vlookup including a couple of ways to streamline, using the Vlookup function and then I’ll close up by introducing the “Match Function” and see how we can use the match function to greatly improve the integrity of our Vlookup functions and just make lookups easier in general. I’m going to be teaching from Excel 2010 today, but pretty much everything I cover is going to be in the same location in Excel 2013 or Excel 2007, if there are any big differences then I’ll make notes on that on the corresponding slides. The biggest difference that you will note is that, Excel 2007 does not have a “File Menu” so during this presentation if you see me click on the file menu or I mention the file menu and you’re using Excel 2007, just note that you will click on that round button that’s called the “Office Button” instead If you happen to still be using Excel 2003, then some of the slides will have notes in blue along the bottom and those are the equivalent steps that you would carry out in your version of Excel. One of my favourite ways to improve the integrity of my spreadsheets is by way of the Vlookup function. What Vlookup allows me to do is look up data based on criteria that I provide and so, for instance let’s say that I need to return an account balance for account 40100 for the month of March, what I could do is, in cell B2 I could write a simple formula, I could say equals H2 and that would then return the value from column H for the second row. But if a user copies and pastes this accounting data for a new month and put it through columns D through Q, and maybe there’s a new account and so account 40100 moves down a row, all of a sudden my simple equals H2 formula has an error because it’s going to be returning the wrong account balance. So, instead what I like to do is use the Vlookup function. Now the “V” in Vlookup stands for vertical, means it’s going to look vertically up and down a column for a value that I specify. So we can see that Vlookup has four inputs, the first input is our look up value, think of that as where to look. So, in this case we’re looking for account 40100 As to where to look, we’re looking at cell D1 through Q5, that’s our list of accounting data, and so the lookup value is always sort in the first column of our table array. So, it’s going to look down column D till it finds account 40100 Then we move to the right and return a value from the column that we specify and we do that by way of the column index number. So, in this case it’s going to go to the fifth column. You see I have them numbered: Column 1 is the account number column Column 2 is the account name Work your way over to March, that’s our fifth column. The last argument we have is if we want an exact match or an approximate match, and so in this case we specify that by way of the word “FAULTS” that signifies that we want an exact match. So, Vlookup allows me to just let Excel find my information, rather than me having to go look for it individually and possibly introduce an error into my spreadsheet should that data shift around in the future. If we go to Excel, we can have a look at how our Vlookup function works. So, in our worksheet, we type in the equal sign (=) type “Vlookup” and so, our look up value in this case is account 40100 Where we’re looking? We going to look at cell D1 all the way over to column Q Now, a trick I use sometimes when I need to lock in a cell reference is, I put dollar signs ($) around the table array. So, if it’s a formula I’m going to copy to the right or down, I don’t want it changing from E1 to R5 and so on. So, I’ll press the F4 key, when I press F4 that puts dollar signs around the cell references. So, probably I should a dollar sign in front of the A as well. But the trick that we learned just now is that, if we highlight our cell references, press F4, each time we press the F4 key it toggles the different arguments there. So, the third argument we learn is our column index number, so we going to put a 5 there because we want data from the fifth column and then we’re going to type the word FALSE because we want to specify an exact match. We press “Enter” we can see that... look down column D for account 40100 when it found a match returned for a value for March. So, if I change this to 40400, then it works its way further down and turns that value, change the value to 40100 and it goes back up to the top. Approximate Match Another use for Vlookup is to perform an approximate match, and an approximate match is when you’re not looking for a specific number, but rather you’re looking for a tier that a number falls into, such as, trying to determine the income tax rate for an individual or a company. So, on this slide we see, we’ve got a tax table of 2014, income tax rates a key when we’re looking for an approximate match is that each of our tiers has to start at the lowest level. So, notice that the table starts out at zero (0) very important that we start from zero, so in effect from zero to 9,075 (nine thousand and seventy five dollars) is the 10% bracket. From 9,076 to 36,901is the 15% bracket and so on. So, if someone is making 150,000 dollars a year and we want to determine which tax bracket they’re in, we can use Vlookup function to do so. As we’ve seen Vlookup has four arguments, first argument is what to look for In this case we’re looking for their income tax amount, their income. The table array is going to be two or more columns, and so in this case we just have two columns because we have our income tax brackets in column A and our corresponding tax rates in column B So that’s why our column in index number is 2 Now, typically with Vlookup we’re going to put zero or the word FALSE because we want an exact match. But not in this case, in this case we’re looking for an approximate match and that’s why it’s very important if you want an approximate match to put the word TRUE or a one (1) or you could just leave out that fourth argument because if you only list the first three arguments then Vlookup does return an approximate value, so that is a risk of Vlookup that if you’re looking for an exact match, we always make sure that we include that zero or FALSE So, probably for best practice always include the argument explicitly rather than leaving it out and in inn overtly getting an approximate match at some point when you really intended on an exact match. Because Vlookup just returns result and it’s kind of on you to verify the proper result. To see this in action we type in our Vlookup formula, so type in equals (=) Vlookup And what we’re looking for is going to be this income amount, so it’s going to be the value in D4 Where I want to look is cells A3 to B9 So, again my tiers start at the lowest value. If I were going to copy this formula down, I would press the F4 key and add dollar signs around that range there, tell Vlookup that I want data from the second column and then I can put a one (1) here in place of the word TRUE, the 1 signifies an approximate match, true does as well. We press enter just to show you that formula, going to pull it over on the screen here And so, if I put in, we’ll just make up an amount, 85,000 that person is in the 25% bracket because his there between 36,901 and 89,351 Put in our 150,000 example that’s the 28% tax bracket, if someone is in the 500,000 tax bracket then that moves him up to the very top tier. So, that gives us an example of using Vlookup for an approximate match. Streamlining Vlookup Now, that we have a sense of how Vlookup works, let’s look at how we can streamline the formula You’ll notice that in this case it’s the same arguments, the lookup value in cell A2 For our table array though I’ve left out the row numbers, so I’m just referencing column D through column Q, that allows me to future proof my Vlookups, so that way if I need to add more rows later on, I don’t need to go back and change the Vlookup function. So I can still put dollar signs ($) around the D and the Q if I choose to, to lock those in, in case I’m copying the formula from side to side but in this case we also see, we still have our five because we referring from today from the fifth column. But then, in place of the word FAULTS, you can see I put a zero (0) there The zero is a shortcut for specifying that we want an exact match, we can either put the word FAULTS or we can put zero (0) there to specify that Vlookup should give us an exact match. We’ll take a quick moment and type out the formula here, so as before =vlookup The lookup value is whatever is in cell A2 This time our table array clicking on the worksheet frame and going across over to column Q Now, another benefit to selecting the worksheet frame is you’ll notice as I’m highlighting across, if I stop at March, you’ll see there’s a five, this is 5C It signifies that I’ve selected five columns, so I work my way across, that gives me the value for my fifth, my thorough regiment which is the returning day from the fifth column and then I put a zero (0) in place of the word FALSE and press enter We see that my formula works and so, if I were to go in here and change that into a 3 Now it’s returning data from the third column, and so now my formula looks like this where it’s got the D and the Q So if I put those dollar signs ($) in and select this cell here and press F-4 and put a dollar sign ($) here and then just to show you once again what the new formula looks like There’s where we can see that formula, make sure we not obscuring anything So we can take this formula and copy it across or copy it down and our column reference would remain static. So, if I change this to 40400 then we see what it’s going to look like in column D, when it finds a match goes to the third column here.

## Learning Objectives

- Identify situations where VLOOKUP may return an error or omit desired data
- Improve the integrity of VLOOKUP by way of the MATCH function
- Make critical distinctions in your data, such as numbers stored as text

## 54 Reviews (160 ratings)Reviews

## Prerequisites

**Course Complexity:**Intermediate

Prerequisite: Basic proficiency with Excel

Advanced Preparation: None