Learn Vlookups in 7 Minutes (Microsoft Excel)

okay so this lesson is on vlookups vlookups allow you to search for related information in your spreadsheet or in other spreadsheets so an example might be you have a name and address in your sheet but you need phone numbers from a separate sheet some phone directory somewhere okay so the first thing we're gonna do is look at the formula and we want to focus in on those colored items so there's a lookup value a table array and a column index number and the end we always put false on it I'll explain that later but that's not as important now so lookup value is is you're telling what you're going to search by so in this case when you look for a phone number you're going to search for the customer number search by the customer number because that's how you know if you have the right phone number and the table array is where the what table has a desired data so in this case it's your phone list and the column index number is what column is the desired data in so when your phone lists the phone number is going to be in the second column so your column index number is just two and that's what it would look like so you've got your lookup value your table array which is your phone list and then your column index number which is two okay so where you are in the customer sheet what we're gonna do is take a look at the phone list real quick just so you can see what that looks like basically have customer numbers and then phone numbers the way that we're gonna find the phone number is by that customer number because it's in both sheets so we'll insert a new column and we're gonna call it phone number and then in the first blank cell we're going to start our vlookup formula and then our lookup value if you recall is what we're going to be searching by and in this case we're searching by our customer number so in our current sheet in the customers list we're gonna hit the customer number the first or the second row there and then our table array is where our desired data is gonna be coming from in this case is the phone list so we'll select both columns in the phone list you want to select all the data that you might need and then the column index number if you recall is the column that has your desired data in it so again we already have the customer number we're most interested in the phone number so in this case the column index number is going to be two that's really what we want to pull type two there and then we'll put false at the end and then we'll hit enter and you'll see the phone number appears for Cornelius and then we'll use the autofill to let Excel do the rest of the work for us and you'll see now that the phone numbers have filled out all the way down what I want you to notice though is if you scroll down a little bit you'll see an error that appears and that's because Carlita one of our customers does not have a phone number listed in our phone directory or our phone list so we can do to fix that because we don't want that weird error to appear is in our will change our B lookup will add something to it well I had what we call an if error we'll put that right now right after the equals sign but if air and then a parenthesis and what it's going to do is to check to see if an error appears in that vlookup formula so we put if error at the beginning and then right after a reveal lookup function we're going to put a comma and then in quotations we're gonna put what we want Excel to put in each cell if it finds an error so if it can't find the phone number and our phone list let's just have it say no number I've actually fixed my quote ation marks there which is good so we're sandwiching that if air inside the vlookup function so we fixed it in the first cell there now we want to do is use our autofill function to make sure that formula updated formula goes all the way down and now you see that Carlita it doesn't give us her number doesn't give an error it just says no number just a cleaner way to to handle it and again so we sandwiched the vlookup function inside the if error we've nested it as they call it and now the final thing that we want to do is to select all of our phone numbers copy them and actually paste them right where they are and then we'll see a little paste options button up here and we're gonna do is select paste as values and the reason why we do this is because we want to get rid of our formulas we just want to leave the phone numbers because if something was to happen to our phone list that it got removed or deleted it's gonna throw errors for us so we want to get rid of that formula and just paste it as values so that's just whatever it returned for us forget the formula we we don't even need it anymore we we've got the phone number and we want to make sure that if something happens to our phone list that it doesn't in our customers sheet here give us an error now we're going to do is go to the orders tab so what we want to do is in the orders tab pull up product price we don't have that currently are in our orders list right now it's actually in a separate sheet called the just products so this tells us unit price by product so go back to our orders tab and in that unit price field we're going to start our vlookup formula and then what we want to search by our lookup value is the product number because that's how we know any other sheet what price we should be pulling it and then our table array which is where our desired data is coming from is over in the product sheet so we'll go to the product sheet and select both of the columns and then our column index number just like in the last example is gonna be two because we don't we don't care about the product number we don't want to pull that over we want to pull the unit price over which is in column two of our table array and again we'll enter at the end of this false and I wouldn't worry too much about why we do that it's just to make sure we have an exact match and it's not approximating anything okay so it worked and we'll use the autofill function to pull everything down and then the final thing that we're going to do is actually change the total cost because now we have the quantity that was sold and the unit price so we're going to just multiply those two type equals quantity times unit price you

Channel: Cody Baldwin

Views: 393,824

Rating: 4.9235883 out of 5

Keywords: Microsoft Excel, vlookup, vlookups, vlookup tutorial, what is vlookup, how to vlookup, vlookup for dummies, vlookup example, how to use vlookup, vlookup formula, vlookup function, v lookup, how to do a vlookup, vlook, how to do vlookup, vlookup examples, using vlookup, lookup function, vlookup help, vlookup explained, vlookup syntax

Id: NN4sq0txjgs

Length: 7min 45sec (465 seconds)

Published: Tue Jan 19 2016