Viewers: in countries Watching now:
Whether you're a novice or an expert wanting to refresh your skillset with Microsoft Excel, this course covers all the basics you need to start entering your data and building organized workbooks. Author Dennis Taylor teaches you how to enter and organize data, perform calculations with simple functions, work with multiple worksheets, format the appearance of your data, and build charts and PivotTables. Other lessons cover the powerful IF, VLOOKUP, and COUNTIF family of functions; the Goal Seek, Solver, and other data analysis tools; and how to automate many of these tasks with macros.
Sometimes you need to use the VLOOKUP function to match data exactly. In this list, in columns A through I, we need to find the department for the products showing in column A. In columns K, L and M, we've got a complete list master inventory, some 90 rows or so of all of our products by department, by category. What's not broken out there is the various colors which are implied and so we do see more data over in column C. So when we need to make a match, we want to use VLOOKUP but we've got to make sure that the data matches exactly.
Unlike comparing numbers where we sometimes search for breakpoints if it's a text entry, it's got to be an exact match and with certain kinds of numbers like ID numbers, Social Security numbers, they too must be exact matches. We use the VLOOKUP function differently for exact matches than we do with approximate matches. Now to make this even clearer, I'm going to hide some of the columns, column C, D, E, F, G simply Right-Click and Hide for the moment, so we can focus on the idea that we want to use the VLOOKUP function in column H then in column I to look up the information from column A and return the appropriate department and then category.
So I'll make the column a bit wider here, also zoom in a little bit using the Zoom Slider bar in the lower right-hand corner. As we're about to look for the department, the table reference that we will be using will be columns K and L. We don't need the category just yet, VLOOKUP in cell H6=vlookup(. The value we're looking up, the text we're looking up is in cell A6, click it or type it, comma. Where is the table that we're using? It's in columns K and L. There's nothing else in those columns so we can simply drag across, hold down the Left Mouse Button and drag across columns K and L and we see that indication in the VLOOKUP function.
That's where the table is located, comma. Which column of the table has the answer that we're looking for? We're looking for the department entry that's in the second column from the left so we put in 2. With an exact match, you need four arguments sometimes called parameters in the VLOOKUP function; comma and you'll see a pop-up tip. False for exact match then we would click this and then tab into place. The word FALSE doesn't exactly scream out as if to say it means exact, but that's what it does mean.
If you prefer to use zero, fine, you can use that. But neither one of them really makes the association that you might expect it to. In other words, it does find the exact match but that's not exactly that something that you would know obviously. Zero or false will work here, that means the matching must be exact. It can't be close as it might be with numbers. And when we press Return here, we should get a correct answer. Bamboo Arm Chair is in the Furniture Department. We can copy this down the columns simply by Double-Clicking. Because the VLOOKUP table reference was a column reference, we don't have to make that an absolute reference.
Now using the same function in column I, we could possibly just copy this portion of it. I'll press Ctrl+C and Escape. We'll need to make an adjustment. Let me make the column narrower and make column I wider. This time we will have to refer to a three column table, so if we were to click here, type "=" and press Ctrl+V to paste, of course we could have been typing all this too. Instead of columns K through L, we want to make sure that that's columns K through M. We want our answers out of the third column. This becomes a 3.
Zero still means exact match. Remember it can be zero or false. As we complete this entry, it's in the Dining Category and Double-Clicking here will copy this down the column. It looks like we carried some formatting with it but we'll adjust that later. So VLOOKUP, we've seen two examples here of how we look up data in a table and as always with VLOOKUP tables, these tables might be elsewhere, although it's certainly handy and convenient and easy to check our results when the table is nearby. If ultimately you want to move this table, if you move the table, for example rightward a few columns or possibly hide this or even drag it on to a different worksheet, it functions as written, should automatically adjust and the answers will continue to be correct.
There's no question that using VLOOKUP for an exact match is always required with text and it will be required if you're trying to look up ID type numbers. They've got to be exact as well.
There are currently no FAQs about Excel 2013 Essential Training.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.