From the course: Excel: Advanced Formulas and Functions
VLOOKUP
From the course: Excel: Advanced Formulas and Functions
VLOOKUP
Let's talk about VLOOKUP. This function is often the turning point for a lot of Excel users. I know it was for me and I've talked with a lot of people. VLOOKUP has multiple components to it and it's not so straightforward. Like if you have SUM, you can go to a cell, click equals SUM, open parentheses, highlight your range, boom, then you've got your sum. But VLOOKUP requires you to slow down and think. And once a person has that, and they're comfortable with VLOOKUP, they are like wide open. They are free to go just about anywhere they want in Excel. So I'm going to help you with that turning point if you aren't already there. But I also have to say XLOOKUP came out a few years ago and it was designed to replace VLOOKUP because VLOOKUP has some weaknesses and I will point those out. So let's get into the data. Enough of me talking. All right, we have this data. We've got CC5M0749. The transaction was for $84.14. We want to get the category and the level of the transaction. Okay, real life situation. You've got a report that came in with the ID and the amount, but now we've been tasked to match up the category and the level. Oh, okay, over here. We have the ID, the name, and the specialization. The specialization is the category. That's what we want to retrieve. And then for each amount, we want to assign a level to it. Okay, the specialization data is not in a table and that's deliberate because I need to show you some things. And this data. Yeah, let's go ahead and put this into a table. The cursor is within the data set. I'm gonna go format as table. Let's grab the black one. Table does have headers. Yes, okay, get rid of the filter buttons. All right, equals VLOOKUP, double click. Look up what? Look up that first ID, comma, the table array. Where do we want to look for that ID? We wanna go over here to this data set. And for VLOOKUP, we have to copy the data that we wanna look up and the data that we wanna retrieve. It's all gotta be here. Even though we don't want the name, we have to copy that. And then I'm gonna hit F4 because when the formula goes down the column on the transactions page, I do not want this moving on me, all right? Comma, column index number. Which column do I want it to bring back? I want one, two, three. Specialization, that's the third column, three. Comma, range lookup. Do I want an approximate match or an exact match? I want an exact match and I'm going to type zero and then close parentheses, enter. All right. CC H2 V UK I is saying electronics. Let's check that. Let's go back. Yep, that is Jean-Pierre and electronics. Good. Now, let's go back to transactions. Okay, got food safety, electronics, arts and crafts. All right, so we have some NAs and let's say that we know that NAs means that this transaction was processed by somebody who no longer works here. So we're gonna have to set these aside and figure out the category separate. Okay, But what we can do is go up to the formula and wrap this in if-n-a Because it was not found Open parentheses. Okay, we have the value and what do we want to put in Replace of just the hashtag in a error say ah no longer employed and it has to be in double quotes and close parentheses for the if in a and enter and notice because this is in a table I made that change in one cell and the formula Has populated throughout the entire column double-click there. Okay, and Also, let's ask why would we want to do something like this? Well, we might want to use a pivot table to compare the food safety values against electronics and event planning, etc. We might only be concerned about automotive data and we need to get everything categorized so that we can then filter everything else out. Lots of reasons why we'd want to match this up. Okay, now we need to level. Now we're going to do something different with the VLOOKUP. Go over to LEVEL, EQUALS, VLOOKUP, double-click it once more. Look up what? We want to look up the dollar amount, comma, the table array. Go back to REPS and LEVELS, highlight both of the columns, comma, column index number. We want the second column, comma, and we want true for an approximate match. What that will do is from zero up to a hundred, the assignment is going to be L1. So we don't need, say, an $83.04 specifically in this table when we do an approximate match. So I'm gonna go and put a one for true because I want the approximate match. close parentheses and enter. There we are. We have an XL here for $736.66. Let's look at what's happening there. Anything $700 and above will get XL. And one thing about VLOOKUP, in order for the approximate match to work, this column does have to be sorted ascending. So that's VLOOKUP for you. Let's go back to the transactions. And a next step could be to wrap the level in an IFNA and pick what we want that to show instead of the error message, or we can leave it as an error message. But right now, we've got what we needed and VLOOKUP made it pretty simple. One requirement of VLOOKUP, the column that has your lookup values has to be the left most. And what you want to retrieve has to be to the right of that. There could be one, two, 50, how many ever columns to the right. but the lookup cannot look leftward.
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Download courses and learn on the go
Watch courses on your mobile device without an internet connection. Download courses using your iOS or Android LinkedIn Learning app.