Instructor for this course
more

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

56 Reviews (163 ratings)Reviews

5
Member's Profile
Would be nice to mention about Hlookup's and any differences. Great refresher course after not using them for a while
3
Member's Profile
Stated learning objectives were met Stated prerequisite requirements were appropriate and sufficient Program materials were relevant and contributed to the achievement of the learning objectives Time allotted to the learning activity was appropriate Instructor(s) was effective
5
Anonymous Author
I've taken several excel courses from David Ringstrom and they are always informative. In this one, the slides are very busy so not easy to use as a reference going forward, but better than some of his other courses.
4
Anonymous Author
I use VLOOKUP periodically in my job and I used to have to find an old spreadsheet to remember the sequence of values in the formula. Not anymore. Now i just go back to David's presentation for a quick refresher.
4
Anonymous Author
Excellent intro (or review) of VLOOKUP. The explanations of the various nuances of VLOOKUP were clear and well-explained. The examples provided in the presentation materials definitely enhanced the presentation.
4
Anonymous Author
Useful and powerful tools simply explained. Some syntax needs to be practiced to ensure proper results and trouble shooting longer or more complicated commands can be troublesome but definitely worth the effort
5
Member's Profile
I don't use vlookup often but seem to have issues when I do. This lesson gave me a better understanding of the function. I can see it is useful for a number of purposes. Outstanding presentation.
4
Member's Profile
The information was good, but there was quite a bit of repetition. With access to the course and reference material, I can come back later. I don't need the same thing repeated three times.
5
Anonymous Author
Nice thorough explanations and examples of using vlookup. This course also showed common errors and restraints of using vlookup and how to avoid and fix them. Good course overall.
4
Anonymous Author
I did not think the question about what version of excel a formula is first available in was very interesting or challenging information. There is an error on slide 2606.
5
Member's Profile
Very helpful! I've always felt VLOOKUP was a monster and didn't know how or where to start learning it. Now I feel like I'm prepared to use it in my everyday work!
5
Member's Profile
The course was very informative but a bit too fast paced for an individual who is not an expert in Excel. Great resources are available for future references.
3
Member's Profile
A good refresher for Vlookup, however, The IFNA Function module seems to have completely repeated itself and is twice as long as the information actually is.
5
Anonymous Author
This course was extremely helpful. I learned that there are many more functions that can be combined with VLOOKUP to get more accurate results in Excel.
4
Anonymous Author
There was a lot of repeated data given. Made the course feel dragged out. Also slides had typos and/or wrong info stated in few places.
4
Member's Profile
I enjoyed this course; however, I believe basic background knowledge is needed in order to follow, due to the pace of the training.
4
Member's Profile
note for instructor: lesson VLOOKUP with IFNA repeats from 2:35 to the end, and did not show the demo of the IFNA function
4
Member's Profile
vlookup explained very well. Some other functions were introduced too which may not be applicable for everyone.
3
Member's Profile
This course content could have been provided in a more clear/concise manner, but still got the job done.
3
Member's Profile
Difficult to follow instructor as mutliple screen shots are applied to each slide in the presentation
5
Anonymous Author
I liked that the instructor went over each topic and then followed it up with an example in Excel.
4
Member's Profile
Very helpful course in learning how to use VLOOKUP and also making the usage more efficient.
5
Member's Profile
I didn't expect to learn anything new, but I didn't previously know about the IFNA formula.
5
Member's Profile
Great overview of vlookup function, its good and bad points, and the alternatives to it.
5
Anonymous Author
I learned a lot of formulas or short cuts within this course. Very good information.
4
Member's Profile
A bit hard to follow in some places but overall, another very good presentation
5
Member's Profile
Very helpful in understanding Vlookup and the nuances and errors.
5
Anonymous Author
Made me aware of additional options when using VLOOKUP.
5
Member's Profile
great info. easy to follow and apply to what I do.
5
Anonymous Author
A good refresh and some new tricks towards the end.
5
Member's Profile
Great class to get familiar with these functions
4
Anonymous Author
Need more spreadsheets to practice formulas on
4
Anonymous Author
A bit basic but I still learned a few tricks.
5
Member's Profile
Great course! Very informative and helpful.
4
Anonymous Author
Easy and understandable training. Thank you
5
Member's Profile
Very good primer for the Vlookup function.
5
Member's Profile
David is great at teaching Excel courses.
5
Anonymous Author
Learn new useful functions of vlookup.
5
Anonymous Author
A simple process for a powerful tool.
5
Member's Profile
Good overview of the vlookup function
4
Anonymous Author
Good info to put to use immediately
4
Member's Profile
Very helpful tips using Excel
4
Member's Profile
clear slides and instruction
4
Anonymous Author
Very insightful subject
3
Member's Profile
it was informative.
4
Member's Profile
I like the course
4
Member's Profile
A good refresher.
3
Anonymous Author
Nice refresher
5
Anonymous Author
Decent course
5
Member's Profile
Great detao;s
4
Member's Profile
Good review.
4
Anonymous Author
great course
4
Anonymous Author
Good course
5
Anonymous Author
Good Course
5
Anonymous Author
Good
4
Anonymous Author
Good

Prerequisites

Course Complexity: Intermediate

Prerequisite: Basic proficiency with Excel

 

Advanced Preparation: None

 

Education Provider Information

Company:
Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact:
For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Course Syllabus
INTRODUCTION AND OVERVIEW
Excel Efficiency: VLOOKUP
  8:48Text vs. Numbers
  8:12VLOOKUP Errors
  7:35IFERROR function
  5:05IFNA Function
  7:21Duplicate Data - MATCH Function
Conclusion
  2:40Recommended Resources; Closing Comments
Continuous Play
  48:14Excel Efficiency: VLOOKUP
SUPPORTING MATERIALS
  PDFExcel Efficiency VLOOKUP-Slides
  PDFExcel Efficiency: VLOOKUP Glossary/Index
  xlsExcel Efficiency VLOOKUP-Excel 2003
  xlsxExcel Efficiency VLOOKUP-Excel 2007-2013
REVIEW & TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM