Excel VLookup Formulas | Steve Chase | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

4 Lessons (23m)
    • 1. Intro

      0:44
    • 2. Vlookup on Product prices

      10:14
    • 3. US States Vlookup

      3:16
    • 4. Common Mistakes working with the Vookup Formula

      8:28
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

292

Students

--

Projects

About This Class

This course will help you learn how to use the VLOOKUP. You will discover how to avoid common mistakes when working with the VLOOKUP as well as tips and tricks with making data tables to help you enter the formulas quicker.

Meet Your Teacher

Teacher Profile Image

Steve Chase

Always be learning and growing

Teacher

Hello, I'm Steve. I grew up in Dayton, OH but now I call San Antonio, TX home along with my entrepreneur wife, Erin, and our 4 sons. I am a QuickBooks Online ProAdvisor. My #1 Clifton StrengthsFinder skill is Learner which comes in handy while teaching on the SkillShare platform. I have my Microsoft Office Master certification. I am an active volunteer leader and Boy Scouts taught me a lot while earning my  to Eagle rank. I enjoy drinking coffee, reading books, visiting art museums, and doing math problems using a brand new sharpened #2 pencils.

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Intro : Hi. My name is Stuart Chase, and I'm a Microsoft Excel expert certified with the Microsoft Office specialists. Master. Sir, Vacations. I like to invite you to watch my Excel, the lookups course here with sculpture where we'll look at how to create a V look up formula. Understand some common practices of using be lookups. We also explore different tips and tricks to make your work easier in excel. And lastly, I'll share with you some common mistakes that are easy to make. And if you look at how you can avoid those mistakes going forward again, my name is Steve Chase. I'm glad you're here with us and other bite you to stick alone, so continue watching the lessons ahead of you. 2. Vlookup on Product prices: gonna look at creating of the look up formula in this activity here. So first, all you're probably watching this video if you have lots of data in an Excel file, so just kind of get some bearing straight here, you're gonna want tohave a list of data that is clearly defined with header name. So, for example, this will be our header. We get a product number, product name, a product type and a price. And, you know your example of data might be employee data sales data scientific. It's so powerful you could have so much data these days, right? And it's not any problem getting it into Microsoft Excel without having a type that so more likely you're gonna import a data list from another source. And the key to doing a D look up is making sure that your first call, um, of your data set has a value in it. That's unique. So when we're looking at these product numbers here, we ought to expect that there should be no duplicates in this product list here. If there were duplicates, then that would be a problem, because, um is going to look down vertically to get to the 1st 1 that we tell to seek after. So the the look up stands for vertical look up, and the way it works is you're going to tell it. Basically, um, I'm gonna you know, do you tell where to start? It's gonna go down until it finds whatever it is you're telling it to look for. So in our example, we're gonna tell it. Toe, look for WB 38392 Right there. And I've got it also referenced over on the right hand side as well. And we just want make sure that it's typed exactly the same way in both locations. If there was an extra space after it and suddenly that little tricky things like that we're gonna mess up. You can use some other formulas, the trim function to help clean it up. But right now, just let's assume that we're dealing with a clean data set, and what we look for does match a list here. So where do we start? We want to start off RV, Look up in a blank cell or silver. You like the answer to be. So what I'm gonna do is going to come over here and I'm the click right here. Now we have to kind of know, what do we want? Uh, are we looking up the price? Um, are we looking up the manufacturer? We looking up something on the same roads, so the key is we can return once it finds it. We're until the d look up to go in other directions or make a right hand turn and go over as far as you want. So let's say that we want to look up the item price. That's would be a popular one here. So I'm just gonna come over here and type price hit, enter. And right now, I'm currently and sell h two. There's no ah formula there. So I can start off by clicking on the formulas tab, then look up in reference. The very last one here is the V. Look up. So it tells us, looks for a value in the left most column of a table and then returns a value in the same row from a column you specify. By default, the table must be sorted in ascending order. Okay, so I'm gonna click, be look up and we get a little arguments box that will pop up here. Okay, Now I'm using a windows machine that you're on the back. It's gonna look a little differently, but you'll be able to still ah, follow along here and plug in your arguments. So we need to tell it. Basically, what are we looking up? What is our look up value And 99% of time you're going to refer to a reference and not type it. So, yes, I could type in this right here. W b um 38 bubble bubble up. But that's too cumbersome and too much or burn 9% time. You want to use a formula that references a cell. So if it changes would get the update all right away. So I'm gonna click on that instead just g two. And if we ever add rows or columns that you two might change down, but it will always show us what we're looking up. That's inside of it. So that's the key, right? There's or inside of it. So that's step one. Know what you're looking up? Step two, understand that you have to find it within it. Another area outside of that plot, and it has to be in the left. Most column of it looks for the value in the Left. Most column of table. We began our video course instruction by looking at the products or whatever it is from the clique in the table. Ray argument. We're in a scroll back over here to side, and we're going Teoh, I'm going to start off in a select A two. That's where we're starting, and we're going to go all the way across. Super important. You go far enough wide to the right to include what you're trying to find and all the way down. So a two colon de 26 if I wanted to start off with a one that's not not probably could've select the header rows. Well, it's it's that would work as well when I'm doing the B look up and I only want the answer one time. Then I'm OK with just referencing the table. Ray, just like that, there'll be some other times when it's super important that you absolute reference your table right in the event, you're going to do multiple references and look ups, and we'll see in another video how to absolute reference that quick tapirs F four and that will give you a shortcut on a PC. Teoh. Absolute references. Fact, while I'm talking about when I just do it real quick, I'm a press selected head F four. And what happens is it puts dollar signs across it. There no harm done in there. That's basically get fixed range there. That that will guarantee it's always gonna look up that no matter how many forms were todo okay, I couldn't resist. I had I had to get that up. Okay, now we're looking at the column index number. I see the law. May students kind of get this kind of mixed up here. They're wondering, what do we do here? Basically, it's a count. How far over to the right do we want Tokyo? So we're gonna go always start with the first column of the table as one. The 1234 Um, I should not have been clicking on that. I'm gonna type before here. Reason is, our price is basically in the fourth call up. And that's a hard number that we typed in there. Do that at this point, there are some other workarounds to put in a match formula that could be relative to the pricing of that. But this point wants to stick with, understand, put in a number four. I'm going to get the price. And last but not least, we have the range. Look up. The range look up is our specifications. If we want to fight an exact match, so most likely that's going to be false. Which tells the formula toe find an exact match and also type zero, which means Santana's fall. So, uh, it's a little bit weird there to think about, but just kind of wrap your head around your telling toe. Perform an exact match when you're looking it up on Lee, look up in this case WB 38392 Don't give us anything that's close around it and so forth. Okay, that is it. So I'm gonna click. OK, I'm going to now discover the result is $8.95 sets for this product here. So the true test is going to be. If I were to delete that and just put in some bogus data, you can see that it doesn't find it. Okay, So what I'll do now is I'm gonna go ahead and type in. Let's tell it to go a bit further down. Let's tell to go down to this one here the C A. So if I type c A dash and 685 on four, 6854 it enter. Oh, look at that. $579 that that looks true. So it went over four populated that both 5 79 That is awesome and noticed that it's not case sensitive. Way told to look up an exact match, I could do lower case there. Okay, awesome. So that is our first lesson on kind of what you could do with the D. Look up, stay tuned for the next lesson that's gonna show you how to do it be Look up. Multiple times we're gonna be looking up United States abbreviations. Um, point the data set there 3. US States Vlookup: Okay, now, this video, we're gonna do the V look up multiple times. Basically, what we have is all of the states are listed here. The problem is, we want to send out postcards to these vendors, and we can't do that. Ah, with the mail merge because we want to get the two character state abbreviations, the American abbreviation. Okay, so, um, we need a data set that it's gonna reference I happened tohave it over here. So what I'm going to do is just do a quick copy of that, and I'm gonna paste it over here for just temporarily information right there. Okay, So the first thing todo is insert column, or he'd like it to go call it abbreviation, and we are ready to rock and roll. So what it's gonna do is gonna look up Arkansas First, it's gonna go down to find it, and then we're just gonna want to reference the second position over. So this is gonna be my table array and look up each state and I'll be able to copy it down . So it's super important that we absolute reference our table ray structure here To do that , it will be dollar sign our dollar sign to colon dollar Sign s you know a way down Teoh 51 there. Yeah, let's do it. So, to begin, I'm gonna select Formulas Tab, Look up in reference. Be Look up! Here we go. My look of value and this scenario would be Arkansas e to Okay, then I'm going to toe Look inside the table, Ray for my next one table. Right? I'm a dual trick. Here a minute. Click on Alabama, used by control shift Arrow down on a PC control shift America Control shift. Right. And then it gets me the data set. Now I'm gonna hit F four because it needs to be absolutely referenced. There column index number again. Think about going down. It's gonna find state hit two over and arrange Look up will be false. Just to prevent if case, there's any miss type ings will get notified of that with an n A. So I'll hit. Okay, so far. Like what I see, I can copy it down also. Um, I wanted to just double click right there. That will copy it all the way down to the till it comes to the last row or a blank row? Yeah, that It's awesome. Awesome. Awesome. Awesome. Okay, so that is the view. Look up. Looking up multiple states. 4. Common Mistakes working with the Vookup Formula: Okay, We're gonna look at some common mistakes that happened with a B Look up. And that is when you reference your table. You don't absolute reference it. So let's begin. I'm gonna start off. And it's same formula you just watched in the previous video. I'm going to click Look up in reference the look up and again, I'm trying to find the privations for the state. So I've got the abbreviations on the right. Here I look up values. What am I looking up slicked Arkansas e to next? I'm gonna select where I'll find it. So in the table or a I'm just going to take my mouth and go across this data set here, Okay? I told you I was gonna make a mistake, so I'm gonna make a mistake. Think of the mistake I'm making right now. As I passed through to the third argument, which it wasn't ready to dio, that's gonna be a mistake. Here I call in the next number is two goes 12 to get to the abbreviation. And we talked about your range. Look up. Most likely, it's almost always gonna be false. Which means find an exact match Okay, so I'm gonna click, OK? And you might think it worked. Yes, it does hurt for the 1st 1 and we'll see how far it's gonna go. Minutes. Copy it down. And Okay, so you're probably thinking, Oh, gosh, what's some? Why are some of them going right? And some of them wrong? Well, the problem is going to be this every time I copy it down the table went down with it as well. So Illinois, it's still in the breath. Now notice that it's going down. At this point. There's two down, so I don't like this. Read this read range here. Has toe always start over with Alabama? You can see with Arizona. This is where it came into trouble because the ah, it's looking up Arizona. And basically it's like, Well, it's not in there again. Ah, the problem is that this table, Ray, you can see it's get going down here starting at the current row. Okay, so I'm gonna hit, undo, And if I needed to edit it, I'm going. Come on, the formula click here and then basically, I need to type a dollar sign in front of our another dollar signing for the to dollar sign in front of the S. I've mentioned before that there's a shortcut key f four, which would do all of that if you select it hit at four on a PC on a PC. Um, so there we have That's the scenario that we wanted Dio. And now if I copied down Look at that beauty right there. They're all matching up O H for Ohio, Maine. Now, one of my favorite things to Dio is to create a table, offer the range your gonna do the V lookups on. So if you're gonna always be looking up data in a table, that's the left. Most calm. What, you're gonna be looking up. Then you can select the whole thing. And if you have version 2007 X seller later, we have a feature called Table Table. The data. You have headers track it and you get to name the table something relevant that you'll use in the B. Look up. So I call it States in the table name. Um I have that. It's going to be I can use that now. And when I'm doing my b Look up. I can just call it states so as I do my V. Look up. Now again, I'm gonna goto formulas look up in reference feed. Look up. Everything's the same. Except for my table. Ray is now gonna be states and watch what happens as soon as I type the letter s, it's gonna recognize that table. Oh, and it tells me Alabama is the 1st 1 that a l and it's gonna reference it. So I see there, right there. So I'm in good shape now, I could just finish up with a two, and it is zero well different, which means false start there. Okay, so I messed up because I referenced F two. Should have referenced E to. I have a hard time to an alphabet going backwards. I couldn't quite think of it either. So that is right there. Find that. Try saying that alphabet backwards. That's that's talks. S e D c B a. All right. And lastly, you might want to know how to kind of do this with ah, with your eyes closed a lot times, I'll just ah, bust out. You do not feel look ups. You just want to just do it real quickly. so I just type it straight, and I always do the V lookups instead of coming up here. Click on. There's nothing wrong with that when you're learning it. But once you start getting this master, you get your eyes closed. Doing it almost you just type equals the look up. I'm that gives you the helpful tips here. That's the first argument, but you do have to remember if you're doing it in the box itself, the former that you do have to remember to type commas or to work so they're type a comma. And then, as I start to type the name of the Table C, how States shows this little table there So as I type it, that's the one at his one. So comma that saves meal time from having who knows, that states could have been on a different worksheet because you can have 20 worksheets and states could have been one of them without even having to go to that worksheet in the workbook. We can reference it, which is really powerful songs, you know. It's the call of number. It's too comma, zero for an exact match or false, and there we go, my friends, I'm gonna do it. Be Look up. Um um, Love, love, Love it. All right. Just be careful here. If we had Ohio with the space after it like that, then I'm going to get any A or Mississippi with whole bunch spaces in it. I'm gonna get a day. Six. So how do you fix that? Use the trim function equals trim, open parentheses. Those prints sees this will remove all spaces in the front or back. And these are now no spaces after it. So I copy that, and I paste right on top of it. The values when I do that, then all of my spaces go away. And I've got a great way to get the right data sets to use the trim function. If you're dealing with imported data, that's not correct with extra spaces on it. All right. I hope you enjoy this course You learned how to do that. Be Look up with products. You learned how to do the, uh do you look up working with us states with multiple references. You learned about the absolute referencing of a table ray or using a table self and lastly , a calm. Mistake is looking up spaces that don't match. You get the N A. So thank you for attending this course. And if you're looking for other classes, if particularly if you're into owning your start your own business, I've got a QuickBooks online course out there as well as she can look up as well. Thank you and have a great day.