Today, we’re going to learn how to use VLOOKUP in Excel. Let’s dive in. Here I am in Microsoft Excel. Over on the left-hand side, I have a table with cookie names and also their prices. And over here, I have a list of cookies and I need to fill in the price. Now here, I need to know the price of classic chocolate chip cookie. I can look over here and I can see that it’s $2.50. So, I could type that in, but hey, I don’t have time for that. There are a bunch of cookies I need to fill in. So instead, let’s use a function called VLOOKUP or what stands for vertical lookup. If you would like to follow along, I’ve included a link to this workbook down below in the description. To enter in a formula, we need to start by entering in an equal sign. This lets Excel know that this is a formula. Next, we need to type in the name of the function. This is called VLOOKUP, so let’s type that in and then let’s open the parentheses. Right down below, we see a helpful hint that tells us what arguments we need to feed in to this function for it to be able to work, and the first argument that it needs is the lookup value. Well, we want to know the price of a classic chocolate chip cookie, which by the way is also my favorite. So, this is the lookup value. I’ll select that and that inserts the cell reference into the function. Next, let’s enter in a comma and that brings us to the next argument in the function. It wants to know the table array. This is the table that we are searching against. Now this table over here, this has all the cookies and all of the prices. So that’s the table. Let’s select all of this. Let’s highlight that and that now inserts the table reference into the function. Then enter in a comma. Now it wants to know what’s called the column index number. What the heck is that? Well, that’s the column in this table that contains the value that we want to return. We want the price. Now, if we look at this, the cookie name is in the first column and the price is in the second column. So, we want the second column back. Within our function, let’s enter in a two. Next, enter in a comma. And lastly, we need to specify whether we want an approximate match or an exact match. Now in this table, I want to find a classic chocolate chip cookie exactly as it’s written here. In which case, that means that I want an exact match or false. Here, I could simply click on it right here and that inserts false. Then we could close the parentheses and hit enter. And look at that. The classic chocolate chip cookie is $2.50. Not bad for such a delicious cookie. As a quick tip, if you need assistance writing your functions, you can click right into here and you could fill it in or alternatively, up on the top bar, you can click on this FX icon and this opens up the insert function helper. Here, you could see all the different arguments that are required for this function to work. And one of the neat things, you can click into any one of these text fields and down below, it tells you what it’s looking for in that text field. And you could also see a preview of what’s being fed in. Sometimes that can really help with entering in functions. I don’t need assistance right now, so I’ll close out of this. I’ve got the price of a classic chocolate chip cookie, but we have more work to do. I need the price for the snickerdoodle and also the double chocolate chunk cookie. So right up here, let’s see if we can just copy this formula down. I’ll click on this icon and that copies it down. But oh no, look at the very bottom. It says #N/A. Looks like I got an error back. If I click into the formula, what you’ll notice is the table has moved down by two rows. So, it’s completely missing the double chocolate chunk cookie. So, what happened there? Well, when I copied this formula down, I copied it down by two rows. And here the table reference also moved down by two rows. That’s what’s known as a relative reference. So, what I want to do instead is I want to lock the reference to this table. So how do we do that? Well, right up here, I’ll click into the formula. And here where I’m referring to the table, we can press the F4 key. And that adds a dollar sign in front of the column and in front of the row. That locks the reference. And here I’ll do the same for the bottom corner of the table. So, this is now turned into what’s called an absolute reference. I’ll press enter. Let’s delete these other ones, and now I could copy the formula down. And look at that, it works as expected. When I click into the double chocolate chunk cookie, it looks like the price is three. And here I can confirm that. And if I click into the formula, here the reference to the table has now been locked and it’s looking at all of the correct data. One problem with this technique is if we add additional rows or remove rows, this reference may no longer be accurate, and we’d have to come in and update it every time we add or remove data. Let me show you a better way. Here, I’ll escape out of that and let’s delete these two functions in these cells. Then let’s click into this table of data. Let’s go to insert up on top and let’s turn this into what’s called a table. Here, I’ll click on this and my table has headers. Let’s click on okay. This is now a table. Now we could reference this table in the VLOOKUP formula. So over here, instead of using an absolute reference, let’s remove that. And here, let’s instead refer to this table. I’ll highlight the whole table. And you’ll notice that in the formula, it now refers to table six, all. I’ll hit enter. Now I could copy this formula down and you’ll see that it works correctly. But now if we come in and we add or if we remove cookies from this list, this will still point to all of the correct list. All right, there you go. Before you leave, one question for you. Why did VLOOKUP break up with its data table? It couldn’t find a match. Okay, that was bad.