We're going to look at creating a V lookup formula. And this activity here. So first of all, you're probably watching this video if you have lots of data in an Excel file. So just kind of get some bearings straight here, you're going to want to have a list of data that is clearly defined with header names. So for example, this would be our header, we got 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 data, it's so powerful, you can have so much data these days, right?

And it's not any problem getting it into Microsoft Excel without having to type that so more than likely, you're going to import a data list from another source. And the key to doing a V lookup is making sure that your first column 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 it is going to look down vertically to get to the first one that we tell it to seek after. So the V lookup stands for vertical lookup. And the way it works is you're going to tell it, basically, I'm going to tell it where to start, you're gonna it's going to go down until it finds whatever it is, you're telling it to look for.

So in our example, we're going To tell it to look for web 38392 right there. And I've got it also referenced over on the right hand side, as well. And we just want to make sure that it's typed exactly the same way in both locations. If there was an extra space after it and stuff like that little tricky, things like that are gonna mess up. And 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're looking for does match a list here. So where do we start? We want to start off our V lookup in a blank cell or cell where you'd like the answer to be. So what I'm going to do is I'm gonna come over here and I'm gonna click right here. Now, we have to kind of know what do we want? Are we looking up the price?

Are we looking up the manufacturer or are we Looking up something on the same row. So the key is we can return once it finds it, we're going to tell the V lookup to go in other directions, or instead, 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 in cell h2.

There's no formula there. So I can start off by clicking on the Formulas tab. Then look up and reference. The very last one here is the V lookup. So it tells us looks for a value in the leftmost 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 going to click the lookup. And we get a little argument box that will pop up here. Okay? Now this is I'm using a Windows machine. If you're on a Mac, it's going to look a little differently.

But you'll be able to still follow along here and plug in your arguments. So we need to tell it basically, what are we looking up what is our lookup value, and 99% of time, you're going to refer to a reference and not type in so yes, I could type in this right here, web three, eight, 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, we'd get the update right away. So I'm going to click on that instead, just jeetu And if we ever add rows or columns that 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 we're inside of it.

Okay. So that's step one, though, what you're looking at step two, understand that you have to find it within another area outside of that plot. And it has to be in the leftmost column of it looks for the value in the leftmost column of table, we began our video course instruction by looking at the product. So whatever it is, and we click on the table array argument, we're going to scroll back over here to side and we're going to I'm going to start off I'm going to select a two is where we're starting. And we're going to go all the way across super important that you go far enough wide to the right to include what you're trying to find. And all the way down.

Yeah, so h2 colon d 26. If I wanted to start off with a one that's not not a problem, I could have select the header rows as well, it's, that would work as well. When I'm doing the V lookup and I only want the answer one time, then I'm okay with just referencing the table rageous like that. There'll be some other times when it's super important that you absolute reference your table array. In the event, you're going to do multiple references and lookups and we'll see in another video, how to absolute reference that a quick tip here is f4 and that will give you a shortcut on a PC to absolute reference. In fact, while I'm talking about what I just do it real quick I'm gonna press selected hit f4 and what happens is it puts dollar signs across it.

No harm done in there. That's basically like a fixed range there that will that will guarantee it's always going to look up that no matter how many farmers we're gonna do. Okay, I couldn't resist I had to I had Get that out. Okay, now we're looking at the column index number I see a lot of a 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 to go.

So we're going to go, always start with the first column of the table as one. So 1234 I should not have been clicking on that, I'm going to type a four here. The reason is, our price is basically in the fourth column. And that's a hard number that we typed in there. To do that, at this point, there are some other workarounds to put in a match formula that can be relative to the pricing all that but at this point, let's just stick with understanding you put in a number for I'm going to get the price. And last but not least, we have the range, lookup.

The range lookup is Our specification if we want to find an exact match, so most likely, that's going to be false, which tells the formula to find an exact match can also type zero, which means the same thing is false. So it's a little bit weird there to think about, but just kind of wrap your head around, you're telling it to perform an exact match when you're looking it up, can only look up, in this case, web 38392. Don't give us anything that's close around it, and so forth. Okay, that is it. So I'm going to click OK. I'm going to now discover the result is $8 and 95 cents 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 doesn't find it. Okay. So what I'll do now is I'm going to go ahead and type in. Let's tell it to go a little bit further down. Let's tell it to go down to this one right here, this ca. So if I type CA, dash and 685946854, hit enter.

Oh, look at that $579. And that looks true. So it went over for populated that bo 579. That is awesome. And notice that it's not case sensitive. We even though we told it to look up an exact match, I could do lowercase there.

Okay, awesome. So that is our first lesson on kind of what you can do with the below. Up. Stay tuned for the next lesson that's going to show you how to do a V lookup multiple times we're going to be looking up the United States abbreviations on the data set there.