VLOOKUP: Learn Excel Formula VLOOKUP!

1. Learn Excel VLOOKUP Now: Hi, Welcome to Microsoft Excel's VLookup, learned the basics. So are you starting out in Excel VLookup? Then this is perfect for you. What we will cover in this course are to follow wildcard search, two-way VLookup comparing list and a whole lot more. So, start learning Excel's VLookup now and see you inside. 2. Wildcard Search with Excel Vlookup: Let's dive in into using welcomed search for our village up formulas. One of the flexibilities with three Look up s. For example, if you have a specific search term, you're not sure on what exact terminus you can actually use wild card search characters and make your V look up. Search is very flexible. Okay, so we have over here you're useful data off values like your data table over here. And then we have a couple of questions or examples that we want to solve. So just to give a quick background, the special search characters that we're going to be using for wild card it's actually asterisk and question, like so for the Astra is what this one issues for. IHS. It matches like a random character. It doesn't care on what the character is, and it also messes any number of characters. So it's like we're saying doesn't care if it's three characters or five characters, a slowness. There's something there, it's gonna match it. We're gonna be seeing it in details with example Stater. So it's more flag matches, any number off Attackers, and for the question mark, it's more of a specific. It's like it's matches any character, but it's only one character. So any character and the important thing, the difference is this. Only one. Okay, so let's go straight, for example. So let's say if you're looking over here, let's see what the sales of the person this name ends with our wife. So let's have ah, like a man will look over here. R y r y Oh, there you go. That's Rory. So we want to find Rory and we want to get the net sales, which is 2091. So if we type it in, we look up, look of value. Okay, so we don't know Rory, let's say we don't know Rory, We have a huge less and from the top of my mind is just ending with our y. So I don't know Rory's name. So what I'm gonna do, Yes, I just want ending in our wise I love type in the asterisks, which means I don't care what characters you have in front off the name. What I'm sure is it ends it with our Y. The next thing. So that's asterisk are wife and the table, Ari. So let's just select our entire table over here and the column number. So we want to grab the over here, which is the net sales. So let's go for tree because it's one first column name. Second, this customers and a turd column is our net sales and then range. Look up. Let's put in exact match because we just want exact, like, matching off R Y. You see over here that it was able to search for Rory and grab in Tool 91 so after is really useful for, like, flexible searching. So let's go to our next example. Sales for the person whose name. Nike. It's chases one Hess seven characters. So this is a really good application for the question work because we want a specific number of characters. As for asteroids, that doesn't care, like how many characters you have, but this one is kind of specific. You want seven only. So let's just type it in. Let's just have a quick look. Seven would be Rebecca. Ah, 1234567 That's seven characters for Rebecca and Rebecca, so we want to grab the net cells, which is 22 or four. So let's type it in you look up the look of value, so we don't know Rebecca. What we only know is seven characters. So let's go for 1234567 So what I did was I typed in seven question marks and then the table. Ari again. It's picked the entire table column number. You want the sale? So wishes the Turk column for our net sales. This type of tree and range Look up. That should be false over here, so he was able to search for it. We have Rebecca seven characters, and that's hills its activity to 204 Let's go to our next example Costumers for the person . This name starts with Rick. So if we look red Greg, Greg, we have Reggie over here, and a number of customers is 14. So let's try it out. You look up, look of value. So if we're gonna be thinking about it, we want our question, this rig over here. So we want Type it in. We're sure that it's starting with Greg and we don't care like what characters follow after Rex. So let's use asteroids over here. Table area would be the same one and then column index and stand off net sales were looking for the number of customers, which is the second column 12 Typing Too Exact match for False. And we have the number of customers off Reggie, which is 14. Next question customers for person whose name starts with R O and ends with T. So these kind off a mix off, ending with a specific character and then starting with specific characters, there's like we're sure on one. The beginning is we're sure what the end this, but we're not sure what the characters in the middle so we can apply again the wild card search over here. So we have our oh, so we're sure the beginning, but we don't know what the Middle East. So this is like a perfect use off the asterisk. We're not sure we don't care when the middle this and we're sure what the what the And this , which is t table already, would be the this one over here, and we're looking for the number of customers, which is the second column, and we're using exact match. So if we look for R o N T, that would be Robert and the number of customers 13. And there you have it. Next. This are less example how Maney sales for the person this name contains BB. And this is kind of tricky because it's saying that I just want for the name off the person , like the past. Maybe I don't care if it's in the middle. I don't care if it's in the beginning, and I don't care if it's in the end. So if you're gonna be thinking, How am I going to write our, like my expression to satisfy this problem? And the cool thing is we're gonna be using again our wild card search. We look up, so if we're gonna be thinking again, it's BB. I don't care, like worse. It's situated so you can use this technique. Sasser's BB in Masters What this tells us ISS. I don't care what characters are in the front, and I don't care what characters are in like at the very end. But it doesn't signify that Bebe has to be in the middle because asterisks like it signifies that it doesn't care, like if there's anything in there, or if there's nothing also in there. So which means BB can actually be at the beginning and followed by a neck, a random number of characters. And it can also be the same that BB. It can be started with any number of characters. And it's ending. And BB, thank you. So what? It means this asteroids. It doesn't care if there's any characters in there that matches in a duffel doesn't care if it's any number two. Like even the 1,000,000 characters, it can still practically match it. So if I type in over source data over here. Column index. So what we're after is the net sales. So 123 that's called a number tree and, as usual, should be an exact match. She's false. So if we look over here, the one with BB, it's actually Rubbi, and it was able to match it and then it 207 tree. So just four like another example Over here. Let's say I type, even if I type in even dis expression in a type in Reggie, for example, it's still able to look for Reggie because it doesn't care on what's the beginning and what's the end, and it was able to get in 1538 over here. Okay, so I'll just put in back BB. So wild card searches. It's really flexible to use. If you not sure of what character? If you're looking for in feet, look up. 3. Looking to the Left with Excel Vlookup: okay, we're going to discuss on looking to the left. So we're gonna be focusing on one off the limitations with the look up. Because if you remember envelopes as we discussed a while ago, ISS, if you have a like an area over here, so you have your data over here table and then if, for example, if you're looking for if your basis off data is net sales, you can see over here we have your sales person named number of customers, the sales that Thestrals person has made, and then the profit or loss for each sales person. And let's say, for example, we have a question over here saying we have which person has net sales off 27 to treat. Okay, So if we look manually, you can see over here is 27 to tree this one, and your answer would be Robbie and one of the limitations with feel Look up, ISS. It's like your reference call them Now is over here. And once you find your reference column 207 tree, you're going to be moving to the left to get the name off the sales person, which is Robbie, which currently, and we look up, we cannot do that. One thing you could do is cut the column D, move it to the first column and then use V. Look up. And because we look up, always looks at the first column and then gets your values to the right, for example, in over here you can look up your reference call. It would be the name, and you can retrieve the values to the right over here. So it's like we're cheating in a way so that we can use video up. You just cut the net sales, the column D place it over here and then use V. Look up so that you can reference the first column and then grab the name to the right. Okay, so what I'm gonna show to you is a technique, an alternative technique so that it's we can overcome this if you look up limitation. So for the examples over here, we're gonna be focusing on using to formula lists. You can see over here it's the index and the match formula. So just to give you a quick background on index index is pretty much so. If he type and index over here, you can see that it takes in and Ari a row number in a column number. So it's more flake taking in like this data for examples. If I put in this ari this area of data and then a type comma and then roll number and column number, it's more flak getting the intersection between the two. It's like a coordinate system. If you're familiar with like X Y coordinates, it's it's kind of similar to this one. So if I type in, for example, of one the first row and then get the first columns value, it will give us rightly over here because this one is like first row and the first column for this table of data that we selected. So if I change this, for example, if I change this 212 so it's like we're picking the second column so it's gonna give us eight wishes the number of customers off righty. So if I kept on changing this right now, one tree that would give us the net sales, which is 1389 it is like the first throw, and then we're grabbing the turd column over here. So if I change this now 22 and one, that would give us Ruby over here and one thing to take Note off when you using Excel formulas. If you can see over here. Column Numb is enclosed in square brackets so you can see the square brackets over here open and close. This would mean that it's an optional parameter, which means you have not record to put in a value like tea if you want to use the index formula. So if I remove this one and if I change this one, for example, so it's giving us an error. So if I change this, I just changed its one over here instead of grabbing in the entire table. If I moved is to just one ranges more of like a one dimension like one column Ari fine click on Enter. It will give us Ruby because it's like if if we have a just a one column like data source, if you type in the rule number, it's just gonna go straight. So, for example, over here it's just going to go straight to Ruby, which is number two, Rule number two, and grab its value right away. So we just why column number is not needed all the time. But if you're putting in a data source that has rows and columns a swell that you would need to specify to call them number and then the next formula for us to be able to solve our problem from a while ago if you think off match against all this time in match match, on the other hand, specifies a look of value and then you provide, like a look up area or your data source. Okay, so if I type in for example, let's say Let's pick a random from the person that's a Rory. So it's like we're looking for Rory. Okay? So instead of just typing in explicitly and then they look at Barry would be this data over here. So let's pick in this one, okay? And in match type match time, it's pretty important for our purposes right now. We're always using the exact match because we just want to for excel, to look at this like the this column values over here and then find the word Rory and and give us the position off Rory. It's more like giving us the row number on where it can find it. So he wanted to be exact match because we want to be explicit that look for the exact term off Rory, and you can see over the graph over here that this depends on the match type. Let's say if you put in one, it's gonna be finding the approximate match if it would only work if this is in ascending order and if it's negative one, it's looking for the approximate match. If the list is in the standing order, if it's already sorted and for our purposes over here, we're always using zero, which finds the exact match. And it doesn't care if the list is in like unsorted, like not in the alphabetical order. Okay, so for our list over here is actually not in any order, so we just want to get exact term off Rory. So that's time in zero, and you can see over here It gave us the number six because if you count it from here, 123456 and you can see over here that it's actually Rory, that's at position number six, or you could think of it s role number six. So that is the purpose of match and the goal of this one s. We're gonna be using a combination off index and match over here. So for example, over here, let's let's just try today sec. One of the things that could help you a lot in Excel formulates its once You have a problem , try to dissect them into smaller parts. So, for example, over here, which person has net sales off? 2070 tree. So our main problem first ISS. If you're going to look at this NYT, columns data 27 ITRI you're gonna try to find first, like, where is 2070 tree located? So the purpose of different if we're gonna be using match to find the location of 2073. Okay, so if we type in over here so I'll just do it step by step first. So our look of value, we're looking for 27 a tree, so I'll type in 2073 right now. Since it's a number, it's just typing in. And then look up, Barry, let's pick this column over here. So that's gonna be our like data source. and match Titus, We discuss ISS, it's gonna be a zero. So we want to find the exact match off 27 degree. So I'll just move this one a bit. You'd still get their go. Let's go for answer, and it's going to give us row number 10. So if you count 123456789 and 10. So very haven't that's 2073. But what we're looking for if the name off the person wishes Robbie So to grab that, we're gonna be using a combination of index. So it's like now we have this one over here we have the rule number, which is 10. Okay, so we're gonna be retrieving. It's like we're going to use index and tell an index Look at the valued over here and get rule number 10 which we already have. So if we type in, so I'm gonna be using combination for minutes right now. So you're typing Index. We were gonna need our data source first and the data source the data that for after Yes, in the names column. So let's select the name condom thanki and the next this we want the rule number. And lucky for us, you already have the wrong number, which is given by this match formula. Okay, so we ready. We actually have the complete formula here, So let's just close the formula and click Enter and it's gonna give us Robbie right away. So we just really cool so we could just play around with the formula. Let's say let's just change tool seven tree and we just want sales off 85 and it's gonna give you rebel right away. So is it's more of like a have a fix for minute right now and just type in a value and you can just crab. If you have a really long list, it makes searching very easy for you. Let's go to the next problem who made the minimum sales? So this is kind of more of a complex problem because you don't have an explicit value. So what we're gonna do over here, let's do it step by step again. So just to solve our problem first it's gonna be if we're gonna look manually. It's 885 That's smallest value over here, and it should give us back the name off a bent, So let's do it step by step again. It might look overwhelming at the beginning, but let's go for the first, like part of our formula. Let's do it step by step. So minimum sales. We need another formula, which is minimum because given the values off the Net sales column, we want to grab the smallest value. So let's go for a minimum first, and you could place it in over here, the list of failures. If I go for enter, it's going to give us the minimum value off 885 and the next this we're going to use the same pattern from a monocle, which is some match and then combination of index. So let's go for a match match is gonna look for a specific value, which we already have over here, which is the minimum value. And then next this the look of Barry, which is actually the same Barry over here because we want to get like which rule number ISS, the minimum value located in. So if we go for before we forget, let's add in the match type, which is zero a next tack match go for enter and it's gonna give us row number seven so we can't from here. 1234567 And that's actually what we need. The next step. It's using Index Index like first s. What do we need? Who made the minimum self So which means we need the name off the person. And the column that has the name of the person is over here. Next. This we want the rule number. And we actually had the roll number from this formula over here. So there's no need like no extra step. We need to do just close the formula. And there haven't we have prevent over here and just for kicks. Just for an extra example. If we change this one instead of a men, let's say who made the maximum number of sales just changed his formula to Max and Rebecca . Let's have a look. It's actually like really Rebecca. So if we look Rebecca to 204 have a look at the values over here, and indeed it's really Rebecca that has the highest value. Okay, so let's just put this one in and then actually ready, like jump ahead. Who has the max number of customers. So already given your hint from a while ago. So let's do this again. So, first thing, step by Step Max number of customers. We want to get the max value first. So let's just use the formula off Max over here and our like, what we're after is the number of customers. So we're gonna be using this us, our data source. I'm just dragging this one to the bottom, and then if you click enter is gonna give us 14. We just after the maximum number. Okay, Next thing is same pattern. We're gonna be using index and match. So let's use match first and what we're looking for the value is the max number of customers, which we already have over here. Next thing is, they look at Barry, which is actually also this one. So look, Forgive me, dear. Oh, number off the maximum number of customers. It's going to give us 13 roll number. Turkey's of a camp 1234 until the bottom. This is actually roll number 13. And the next thing yes, we're gonna use the same pattern. Let's use index and then what we're after who So our question is who? So what we're after is the name off the sales person. So which means our data source would be the name column. And next thing is the role number. So same thing. We already have the wrong number over here, so we can just close the formula. And we have Reggie as the person with the max number of customers. Okay, so let's go for our last example. How many customers did the person with the highest profit have? Okay, so this more of a kind of, like twist instead off giving back the name of the sales person. What we're looking for right now is how many customers did this person have? And that person should have the highest profit. Okay, so that's the second problem again. Highest profit. Let's go for Max. We should stats maximum and profit would be from this condom over here. Okay, so let's close this one, and it's 1 to 22 So let's have a quick look over here and there. You have it. Yep. It's actually the highest number over here. And the next thing is, we're gonna be using match to find which row does this 1 to 2 to recite him look of values already the max value. So we have a good and then they look at Barry. We also gonna be looking at this one. And before I forget, that's d exact match, which is zero. So it's on roll number 12 which is the right one We're gonna be using index right now is you don't need the name. What the problem or the question is after is the number of customers of this person. So instead of looking from our previous examples, we're always looking at this one. We're gonna be retrieving the name, but this time we're gonna be getting our value from this column instead because what we want, Yes, the number of customers like the one with the highest profit Hess. So if you look over here, it's 1 to 2 to so which is actually racial with the highest profit. And what we want to return is the number of customers off racial, which is nine over here. So if we type in comma and we have been the role number over here, so let's close the formula and there you have it. It's actually the number of customers off racial, which is name 4. 2-Way Lookup: Okay, so let's talk about getting the intersection off row and column values. So we're going to be having fun again with this table of data over here. Chickens. You over here. This off. So those persons, you have your names over here and their sales, their individual sales, like, for each month from January December. So we have felt call apps update over here. And, for example, if we want to get a specific person and a specific month and then grab the sales for that intersection. So over here, we have an example off the sails off Frosty for the month off July. So if we look at you, lie over here, and then we have Rossi. So our intersection would be $10. So the Kolding, with this oneness, we're gonna be creating a dynamic functionality, and our formula would automatically retrieve the sales amount for the values that we selected. So to simplify the samples or the formulas that we're going to be making, I've went ahead and changed this one, for example, into a second name viable over here. So if I highlighted January to December to call him names over here, you can see list month on our name ball. So we're gonna be using this month later so that once we create our formulas, the purpose of this one is to make it more readable. And you're not just, like, seeing call them numbers and cell numbers like C five d five d 11 something like that. So instead, we're gonna be using last month and it's gonna be selecting right away this highly, the drainage over here and then other viable that I already created. It's this one as well, so we have our list of names, so this is already a like the selected range right away. And over here is I created a drop down over here. It's more like a reference towards the less off names that we have over here. And the purpose of this one is so that we can play around with the formula later. Once we change the values we're going to see right away our assault. It changes based on what we selected on the name and the month dropped down. So it's the same thing over here. It's actually a drop down list of values based on this column. Names over here aren't headers over here. So if we look at the name box again, you can see over here that its value name so value name was simply be whatever you selected over here and then value month would be whatever you selected for the month, drop them. So which is July? This is simply for the purpose of making our formula a lot more readable rather than placing in the cell values. So we're gonna be thinking, how are we gonna be approaching this problem? And what I'm gonna share to you are teach to you is two ways to be able to do this. What we call us the intersection or a two way look up. So first like approach over here is a fee look up and the match. So let's have a think first about the month of July If we're gonna be using match, we can crab this list of column headers over here and then match. Find out where is July situated. So let's typing in. If we go for a match and are looking value, we're gonna be grabbing it from this value over here to which is and it's pretty cool. It gives us value month right away. Okay. And then look up, Ari. Where are we going to look for, like, July? Where is it situated in. So if we highlight this one, and this changes right away to this month so we're picking this range off months over here to call them headers and then as useful, are much type. We're gonna be using an exact match, which is zero if you type it in. Over here. You can see seven as this ISS. If you counted it. 1234567 So it's saying that it's on the seventh column, so Match was able to give us column number seven. So if we go again like the main topic, it's intersection. So we now know that this ISS located in seven column were having a good start right now. And the next question right now is we have the seven column. Now we need that row to grab the value of $10. And here is where V look up. It's gonna come in. So let's have it in right now. So it's type. And if you look up first things first, the look of value, what are we gonna grab the value on and we're gonna be our basis for finding a row number? Yes, What the user has selected for the name. So I've picked rosy right now over here, which is, actually, if you can see over here, it's actually over value name, which is the name that you selected from the crept down. The name dropped down next. This we want to grab the table, Ari. So which means our values overhears I'll just highlight this one. So we look up, let's re highlight that one, because we need the name column included. A swell. Because if we don't have the name column, then how is we Look up, Going to search for the name off Rossi. So we need to highlight this entire table over here and their next let's look for different . We want the column in X number. So we have d card on number right now, which is seven over here. But in a short well, you're going to see that there's gonna be a slight tweak that we need to do. And next this as usual. You want an exact match? We want Rossi to be like search explicitly over this column over here. Let's go for faults. Go for enter and let's have a look when we're expecting. It's Rossi and July, and we're expecting the value of $10. But for some odd reason, it actually got 20 over here. So why is it so? Let's have a look first on the look up if we think about the look up. So we have Rusi and our like data like tables. Data over here is this table. So if we count the number of condoms, we want the value of 10. So we can't. This is the first column. So it just one second column Terry Fort, Fifth, sixth and Seventh. And if you recall, a while ago, our match, like the formula, gave us July as to seven column. But if we count from a V lookups perspective, this is actually column number seven. And oddly enough, it's on June, and the reason why this happened is because RV look up started counting from the name column. But our match formula started counting from January to December. There's a discrepancy if you compare and look from Avila Cups perspective, which start from this column over here which is column B and from a matches perspective, the formula which starts from cotton see so to be able to, like sink both formed us together. What I'm gonna do is I'm gonna add one to the match formula. So instead of going for column number seven, we're telling Villa up instead of going for the seven column, just add one and jump to the eighth column. It's over, adding one so that it readjusts everything and adds one always to our match result. And we will be able to crab our value. So you go over enter. No, we're able to grab the correct value, which just 10. So if I just, like, move this one to its peak November. So if it's November for Rossi, it should give us 38. And for enough, it's now dynamic and changes right away to 38. So you just play around with the value. Just put on anything, the same me. And if we look over here is happy 22. So it's now able to search for any value, and it's really powerful if you want to create, like, really cool reports to your boss and if you just fiddle around with the values. So if I pick in, for example, Robbie over here, it's gonna change right away. Look at Robbie and on the month me, that's 30 tree. So let's put this back again to Let's look for Rusi and let's go back again to our original example, which is July. So that's, for example, for V Look up and match. Another approach that we can do is kind of similar to what we did a while ago is a combination off index and match. So if we recalled again index text in an area values and then the row number in the column number, So we need to grab first roll number and the column number to be able to utilize our index formula. So let's look at our problem. If we think about the column number, we're gonna be looking for column July the same thing for Rosie, which is destro number over here, so this looks like a perfect application off match. Let's try first for the column number, so if we try for two column, we're gonna be looking for the look of value. So which is this one? This is our source Like what we're looking for this July. Next is the look Aparri. So let's just go straight to this one, because this one has our month values. Go for a match type. Same thing you want to exact mash, Mrs Zero. And that gives us a similar values while ago, which is seven, which is the seven column. Next thing is, I'll just place isn't here. First we want to look for Rossi, which is our row number. So look of value would be the name from our name. Drop down. Next thing it's our look at Barry. Delicious our source of names over here. So we have our listening and the match type is zero, which is an exact match. So we just want the exact again. It's really similar to what we did with our previous examples. So it's gonna give us row number four, which is, if you count 123 and four, which is Rossi. So if we go for let's just try it here. If you go for index and the area off sales data over here, see if we highlight this one. If we're going for row number four, so if you count. 12 That's just count from here. 1234 And then we're going to call them number seven. So we're right now And call the number 123456 and seven. So which would give us $10. So if I type in, it's just picked the area first. This one just tracking it over here. Let's go for a column number, which is four. What we got a while ago call. The number would be seven, and that would give us $10. So what I'm gonna do right now is I'm gonna copy in. This is our Roe formula. I'll just copy this one copy and then little do right now is we're going back. This one. This one has our column number. So what I'll do is I'll start using the index formula. Let's use our Ari. This is our area values. Next. This we want our row number. So I have, like, copied of all ago for a row number. I'll just painted in. Okay, so we have this one, our formula for getting the row number based on the name over here. So we're looking for Rossi, which is roll number four and the next one, if the column number, which would get like which column based on the month that the user selected. So which, if you count 1234567 That's the seventh Column. And so if you're looking from a just formed over here, we have all of our parameters. Complete Ari Roll number column number. That's close to formula, and we have the value of 10. So let me just did it this once. And if we change the value start now, let's go for October and it's giving us 24 for Rosie. If we change it to, let's say January the first month it's grabbing us 35 right now. Same thing if we change the name, let's go for and the name That's a roasts. If we look at growths and January, that would give us 40. So it's using match, forgetting first, a column number and then another match. Forgetting the row number. Put this together and you can apply this values to index and it can grab you the two way look up assault 5. Microsoft Excel Vlookup Tips and Tricks: Let's talk about additional tips and tricks that you can do to make your V look up usage even better. So what we're gonna go true right now is what other tricks or what other formulas are functionalities in excel that we can use in combination with V. Look up, even improve, However, if you look up experience, So let's go for a tip. Number one you can see over here that we're using Name ranges for the purpose off readability. So let's have a look at our date agreed over here. So we just have a very simple greed for their simple example. Let's say we have the names for Riley and then Ruby, and then they have the genders listed out over here. So right, deice mailed and Ruby is female. And let's say our problem right now is we have a target off Ruby and our goal off this example or are this exercise is to get the gender off. Whatever replace you as our target. If you look at the data grid, what we're after is the value female. So if we just type in our normal v, look up So this just typing, we look up our look of value would be based here. And then let's go for the table, Ari. So our data set with this one and then column Shane's were after the gender. So let's go for name is the first column. Gender is the second column. So that's number two. And as always, we want an exact match, So we should before us. So if you go for enter, Dad would work. I mean, you would be able to get the female value with no problems. But one thing that you can use to enhance your V look up formula is to use name ranges because you can see over here you can have a look at this formula that we're using cell ranges over here so you could see that B nine a six to B seven. If you're giving this to another person or another user, this formula wouldn't be like, really that readable. At first glance, it's like I am still need to have a look at OK, be nine. Okay, so it's will be a six to B seven. Okay. It looks to be the great over here and the thing that you can use to enhance. Let's just escaped that one. The thing that you can use to enhance the readability of her formulas issues ing name Rangers. So you can see over here we have two examples. Original formula. This is what we created just now. We just place in the normal use Asian. If you look up and just kick on the cells drag and that's it. The clean formula, on the other hand, is we're going to be replacing the B nine, a six to B seven with more descriptive names. So the question right now is how do we do that? So let's try out first with Drippy. And if we look at the tabs over here, just go. 40 formulas Tab. You can see over here. Name, manager, defined name is informative. This one's are what we're going to use for adding a name for Ruby or for our data table later. So let's start first with Ruby. Let's just go forward to find name. So you just make sure you have Ruby selected right now. Go for the fine name you can see over here. You have a new name. So for our purposes, we just want to say value names or something like that is just something descriptive that you want to place in. And then over here, if you just want a place and comment, feel free to do so. So let's say this is the target. Just go for Okay, so you have a look over here. It refers to tips and tricks, which is this sheet over here and then be nine. So looks to be okay. So it's just saying the new name overhears. You're saying vile name is pretty much synonymous with B nine in this worship to just click . OK, so if you select Ruby right now, you can see over here it's now showing up in the name box as that name, which is what we type just now. So we're gonna be doing the same thing right now with our table over here. So let's just highlight this one. If you notice we're picking the same cells from what we use for our V, look up about ago and do the same thing go for defined name in case. Remember, it's under deformities tab to find name. And then let's just use the name table sample over here, and if you look at the refer over here, it's a six to B seven, so we just pretty much correct for our data table. Just go for okay. So if you have a look at the name buffs again, if we highlight, this, one is going to show up table sample, which is what we just type a while ago. And another thing is, if you just let's say you're not sure what are the name Regis that I placed in in this Excel sheet? You just go for a name manager and you can have a look off what we define a while ago so you can see over here just to table sample and Val name, and it's pretty much all of the information is over here. You can even delete them if you're not using them anymore, Mr Kills. Now let's update our formula. There's just did it, this one and we look up. Let's just redo what we did a while ago. If you click this one now, the cool thing with Excel ISS, it's not going to give you the cell like coordinate anymore. It gives you now the descriptive name that you defined, so it's instantly replaces it with what name you have defined for this cell. And next IHS, let's pick the table. And then again, he can see over here. It automatically replaced it with table sample. But you can also do the same thing. For example, you don't want to select the cells anymore. Just type in table sample and it's going to show up and select it for you. Same name column. UNIX number. So it's gonna be gender of second column, and then we want, as always, an exact match, which is false, and it will still work the same, and you can see over here if you highlight it now, it's gonna be a lot more readable. So if we do the same thing, for example, if you just changes to rightly, then we expect our formula to still work. So tip number two making lists, references absolute. So pretty much. The assumption over here is we're not using name ranges. So if we have a look over here, let's just go for the same example from a while ago. Riley and Ruby. And let's say, instead of gender, we have the I. D number and rightly has an I d off one. And then Ruby has a 90 number off to. And our exercise right now is given this greed over here, you want to use video up to get the i D. Numbers off Ruby and righty. So right now, we're just using a small data set and assume that we're doing this exercise for bigger one . Because what we're gonna do is we're gonna type in. It's safe. The formula first for Ruby. So I look up value here, It's Ruby, and it's assumed that we're not using names right now or viable. Zo, over here. So next is the table. So we just select this one. Next is for the column in X number. So we want I D, which is called a number two. And in issues. Will you want the exact match? His fault. So you can see that it worked perfectly fine. That's too over here, which is Ruby s I. D. Number. The question is, for example, if you have a really long list, you don't want to be typing out the look of formulas one by one. But you would use what you do. Yes, go for copy and then just pays the formula over here. So if we go for paces, just paste this one and you would not. It's that it's an A, and if we think about it, we were expecting it to work because we're using the correct. We look a formula which is over here. It worked perfectly fine for Ruby, but the question is, how come it didn't work for righty? So that's double click on the formula right now and have a look, and you can see that the data grid or the table ranged Peter like that. It actually moved one place lower Monroe lower. It's because we if we copy the formula, and if you had a look at the cell ranges have used, it's actually relative right now because if you copy it down, then pretty much all of the cells that we have listed out over here would also move one row below. So what happened was our table also move one row below, and since it moved one rural below, we're looking for the value Friday, And if you see this strange over here, Riley doesn't access in this range, which is why the V look up formula returned back at N A. So one thing that we need to do is to make the list reference absolute. So could see over here to incorrect use age iss we just placed in our normal usage. So nothing special. And then over here, you can see over here the dollar science, another science mean that a few copy that formula to any other cell. It will keep it as constant meaning. You can see over here, meaning that the I seven to J eight won't be going anywhere. It will always stay the same, even if I copy the formula to somewhere else. So let's make the change right now and go back here and one tip ISS instead off because there's two ways is either you typing manually dollar, dollar and a dollar in dollar or what you can do is use at four. So what? I'm gonna do it. I'll just highlight this range over here, and I'll use the F four key and it will do the work for you. So I type in a four, and now it changed it to absolute value straight away. So there's no need for me to type the dollar science manually one at the time. And once we had this for me, I just cook, Enter. Let's have a look. It still works fine. It's getting the value off two, which is true beast i d. Number. Now what I'm gonna do is how copy and that's do the same thing as well. Ago. That's paste this one over here and now it's working. Fine, because if we check this formula, you'll notice that I seven n j eight it's still pointing to the correct table off our data . And then the l ate it moved a row below because we didn't change this one. It's like it's still relative. So if we move this formula one row below and the value of this one would also move along with whatever like location replaced it then. So it's really useful. If you have a like a list reference, it's really helpful. And then you have a really long list. You could just make it absolute so that wherever you pace the formula, it will stay the same for your Table of data tip number Tree, which is checking for errors. So let's have a look first at our table of data, which is the same as a while ago Riley and will be with idea numbers one and two, respectively. And if we look at our exercise over here, it's also similar get idea numbers off whatever names that are listed out over here. You can see over here that maybe we were able to get it because it's pretty much the same as how we used to feel Look up formula. So we're just looking for two. But here's the problem. If we copy this one and reducing the same formula from Amal ago, we're now using absolute. So if we pay, sit over here. We're expecting it to work just fine. And if we pace it and it now, displace it as an A. And the reason for this is our name is Ciardi, and Charlie doesn't exist in this table over here. So which is expected? But if we type in, for example, Riley and that would give us the idea number of one. So let's put this back. So with the value of Charlie, it displays and a because from Villa cups perspective, it's an error. It cannot find the value of Charlie and you can see over here can display errors. Is we just use V look up as normal, Nothing special. It's pretty much the same as what we're doing it before. And here's a tip what we can use right now if the if error formula and it it's applicable to excels without some seven and above. What it's gonna do is if, let's try it out over here. If we type in if error, it shows to us that he passing the value and if the value if error, what is going to do? If it sees an error to whatever value you place in the first parameter, it's going to substitute that right away with the value if error. So it's more of just a nicer way off hiding the error, meaning that if it sees an error, display this text instead. So what we're going to do? Yes, let's just wrap this formula with, if ever so we still have RV. Look up forming over here. If there's an error, what are we going to do? Let's say if you want to display the tax off, not found and now it's displaying. It s not found so pretty much we're combining if error, if we look up so that there's a nicer way off displaying it cannot find, especially for formula. Or if there's an error. Somehow, in your view, look up formula and what we're gonna do. Yes, let's say that's type in writing from a lot of go. And it still works as expected because what if error does is if it doesn't see an error, and it just returns. Whatever output the original like parameter you place, then, which is our vehicle formula, since it was able to find value, which it's one, it returns back one and not our not found text. But if we put back Charlie over here, since it's unable to find any value, it's now going to display the nut found Texas More fleck on alternative texts in case there's an error 6. Thank you and Congratulations!: Thank you and congratulations on completing this course. So I hope that this has helped you in learning Microsoft Excel VLookup basics. So you are able to leave a review for this course as well. It's very easy to do so you can simply go to reviews over here. And you would have a leave Review Board on as well over here so that you can simply click on it and leave a review for the course. So please, please leave an honest review as I would love to hear your feedback. So you can also check out my profile for more Microsoft related courses and to be able to do so, just click on my name, this link over here. And once you click on it, you will be able to see my profile and you can continue your learning journey by going through my top microsoft courses.