Excel VLOOKUP Masterclass

1. Class Introduction: Hi, My name is Jared. I'm an excellent trainer and this is my real cup masterclass. Bucha is it's really amazing function. If you master it, your life will be so much easier. You can do stop in excel in just minutes instead of hours. Isn't that amazing? This class contains several videos, and each of the video is a self contained lessons with examples. If you would like to follow me and try with me, please don't know the war book with a sample data, you can practice with me. So let's start with the first video, which contains the introduction to the vehicle functions. 2. Before you start: Regional and language differences in Excel: is you buy all my training. Participants come from different parts of the world. Let me explain you the differences between different language versions Before we start. All my videos are made in English versions of Microsoft Excel. However, if you use Excel in different language, which is the icons and menus are translated but also the foremost. For example, here you can see the the look of function in different languages in the ankle expression. If I start typing, if you look up, as you can see, it is recognised. If I start typing some other versions, you like German, it's not recognized and it will not work. So if you want to know the name of the former in the version off we're using for our language, Google it and Google the faction and its translation in your language. But of course, if I have an English version and you will have a German version, if I use the vehicle function and said it to you, the function gets automatically translated to your local version. Another interesting difference is the symbol separate. Most of the countries in the world used comma is a decimal separator, so my excel is set up to accept coma. So, for example, if I would like to put 1.2, I put one comma, too, and it's accepted as a number. If I will put 1.2, it will not be accepted, and in this case is you can see converted to date instead. So if you are living in a country where coma is used than your ex Ellis, most probably set up to accept coma regardless off the language versions you are using if you're living in English speaking country that you are most probably using the comma, the decimal separator as they also impact on four Morse, let's check the view. Look up for more here. We can see that there are several columns dividing the individual inputs. This is what do you use when you have decimal comma set is your separator. If you are living in an English speaking country and Oreos decimal point than most, probably you will get. Coma is an individual input separator, so into check if you use your formal us what kind off? What kind of separator does your former except if you're using this insert function way toe at it form us, then there is no difference for you. So as I'm living in a coma country, then, uh, you will see you will see a semicolons here as a divider. But be careful. Check your own so that it works for you as well, Waller. Now I have the same document opened in Google sheets. If you would like to follow this training using Google sheets, that's fine. The functions are 90% the same. Excel formulas are even 99% of same, with notable difference off the X look up function, which is, at the moment off shooting this video not supported by Google sheets, but it can change any time later. The interface is a bit different, but after you get used to it, you can work pretty well with Google sheets as well. And now I'm begin Microsoft Excel and we can get started 3. VLOOKUP intro for beginners: we look up is by far the best. The most useful function in excel. What, me Show you how to use it on this simple travel agency example. What I got here is an order list. Each line represents an order, and I know the name of the customer and the trip they choose. On the right side, I have a price list, which is a list off trips that thes travel agencies offering and its name and price. And my task right now is to fill in the trip name and price for each order. I can do it by hand, but that will take a long time. So let's try to use be look up instead. Let me do it for Bradley here first. And then we will fill it in for the other rose as well. So Viel Cap and View Cup has four inputs Four things to know toe Get the correct trip. Name first thing. Look a value. This is okay. So what? We are looking up. So we would like to know the name for trip I 24. The next thing would be table where to look up. I will select it right here, so we'll be likable. Do is go through the first column off the selected table from top until it finds the right line with the right item, which is here I 24 and then the next question would be from which column off this table should I give you the output? And the answer would be the column age. But in this function, we can just simply say, column age when to tell them the number off the column inside this selected table. So in our case right here, trip name is column number two. The last thing the function is to know is what should it do in case the trip idea will not be found, it broke sumit match means that Excell trying to find the closest value, the nearest value. We don't want that. We don't want to know the approximate street name. That's another case. Right now. We would like to know the exact trip name or if it's not there. We would like to get notified of that, so we'll put an exact match instead of false. I can just simply type zero, which is the same as false but shorter. So that's it. So there's the function to look up the trip name Bradley chose. So what we see is the Cape Ray Iceland and it's correct. So now I will double cricket toe fill it for all the orders is about. So if you double click this dot here on the bottom, you will get function for each of the lines. Isn't that great? I'll show you once again how to use it for price. It will be very similar for more but this time I will use the insulin function is a different way. Help toe enter Formosan Excel. Some people prefer to type it right into the cell, right? Like I just did some people like this, So I would like to show you how to use it. As you can see, I have view Look up here in my most recently used functions because I use it quite often, but you might not have it here and guess you don't see it here. You can just type view, Look up right here and praise Go and it's right here And okay, so you will get like this window with four inputs which are the same like the impulse that we put directly into the cell. So once again we are looking for sleep that Bradley takes, and we are once again searching in the same table right here. But this time we would like to get the output from column number three and once again would like to get an exact match. Okay, and that's it. And what we got is the correct price. Now I double click this dot again to get it filled in all the other orders. As you can see, feel, cup is a very powerful function that can help you eloped. The good news. It's easy to learn. 4. VLOOKUP from a table on another sheet: Bjelica is a function that can look up a value from a source stable and inserted into a destination table. There might be both on the same excel sheet, but in most situations you will find them on separate sheets. You can do that easily. Let me show you how this is a travel agents example and I have my order list here on the sheet orders. And I have my price list here on the sheet prices. And what I need to do is to fill in trip name and trip price for each of the orders. Let me show you how to do it for using we look up across multiple sheets. Ah, hell Recommend using the insert function right here. The here. And as you can see, I have a view. Look up in my most recently used functions, so I can just click, OK and go. If you don't see it here, just type real cub here and click. Go here, click. OK. And, uh so I will look it up for Bradley. So Bradley goes on this trip and I will look it up in the price list right here. It's OK. And see what happened. I see here, prices exclamation Mark A to C. This is something like a calling coat off the other sheet. This tells us. Okay, take this. Information from the price is she Color index number is two. And rental cup is zero for exact match. Click OK and have the results for Bradley. And if I double click the dot here I have the results for everyone. Let's try the price now. And this time I will show you what can go wrong. So you look up once again, I will look up this value and be careful if you switch sheets now see what happens by speech. Switch sheets. Now you see anything I put the here was replaced by the calling coat. Every time you switch sheets, Excel will fill in the calling coat off the sheet and deletes everything you put in there. So what we need to do is click here and put that information, See what just happened. Except put a calling coat. Even though you are referencing the same sheet that's actually not working in Excel, you might not called the calling coat because there could be a mistake, so I would recommend not to use the calling coat on the same sheet, so it's C five. And now let's which for the prices like the prices. And now see what happens if you just stay in the input field off table and switch sheets to continue. See the calling coat switched from prices to orders, and now you are selecting completely wrong array. You don't want that to be careful. So after a finished with that, just click to the next import column. Input number. Maybe you forgot which call number it waas, so I would like to look to switch again. And, as you can see, calling code is right here once again. And if you've no press number three, it will not work like that. The column index number must not have this calling crowed. So dilly that and put three same goes for range. Look up. It's not to be just plain zero for exact match. So as you can see, using V, look up across multiple sheets might not be very user friendly, but after some time playing around with it, I think it's easy toe learn. So that's it. That's how Deal Cup works. Using multiple sheets 5. Common VLOOKUP errors and mistake and how to avoid them: Well, CA is an amazing function, but it does have some trips. I will show you how Toe Evert to most common ones, this is a travel agency sheet and I have two tables here, the order list and the price lists. And I would like to fill in the trip name and poetry prize from the price list. That would be fairly easy if it's like that. But what with the trip, I d would be in the second column right here. Can we still do that? Let's try. So we have you look up here and I'm looking for this trip. I D and I would like to so like the table. But now what next? How will it work? The problem is that you cannot choose which column will be the one that will be searched. Unfortunately, well, cup can only go through the first column out of the table you just selected. And because the trip 80 is in the second column, there is no way for you to search for the trip name, so it's impossible to use view. Look up to search for a trip name right now. You will need to flip back the columns so that the trip I d is on the left side of the trip name. However, we can still search for price. Let me show you how. Because this time, if I'm searching for that and selecting the table, you have to select the full actual table. Can select Onley thes two columns. So in that case, I do have the trip I d in the first column and then the price will be the second column off the selected table. So in this time I can search for it easily. I put zero for exact match and I have the price. So this is how I do that. So there was the one of the traps. So there is no way how to search left out off the I. D. So on it right, it's possible. Let me take it back and I will show you second way how to do it. So I will reveal Cap and I will use a slightly different approach. So I was like that and I will select the table like that and column number two and exact match. And you were like, Maybe that works as well. Why not? But never be happy about your result when it works on the first line. Always check when it works for all the lines. So if you double click that, you will see that your function works for first couple off lines. But then it stops and a invoke up. It means the Valley was not found. So every 06 waas not found you're like, How come it's on the second line? How it will be not found and the singles for the others, like eats wealth like how it can not be found. How come the formula works in the beginning, It stops working later on. It's because off the absolute and relative way off referencing Excel. It works now. But if you double click the formulas on each line, you will see that the array like like the table array from off the source, moves from it. Each line it moves one line down, so each row takes it a little bit down. So it moves relatively with the formula, which is great with this. Exactly what we need to do here on the trip, I d. But we don't want to do that right here. There is a way. How tow. Avoid that. So let me delete those. And there is a way help to avoid that. So let's try once again. So you look up looking up for this in death Ray. And after I saw like that, I need to press f four. So if you press f for you will see these dollar signs right here, what they do is they switch from relative reference toe absolute reference. So now we are referencing the table and it will not move. So the dollar signs here mean do not move the table if you fill in the former So how we can continue like normal. And now, if I double click to fill in, you see it works. But if I use view cup, I personally prefer toe select full columns for several reasons like that. So, uh, likely that and I was like the full columns here. Why? Because first, you don't get this problem off moving space, so you will avoid this problem completely with selecting the full columns. And also the former looks a little bit nicer. You don't have toe worry about losing time, or you have to worry that the table doesn't start in the first line. He was you. Let's say maybe. Ah, loose several microseconds off computing time for searching these four lines in unnecessary way, but in 99% of situation that perfectly OK, so I encourage you to use full columns that will make your life in excel easier. So this is the way how Use view cup. And this is the way how I avoid the two most common traps. 6. VLOOKUP Tips and tricks: There are many things that can go wrong when you use vehicle. Look at this travel agency example. I populated a vehicle function here to search for the trip name in the price list right here, and I filled it. It's OK, and right now what I see is the former is not working on the first throw, even though it's working really great on the other. Rose. And you are like, How is that possible? This is the same coat I 24 24 the same formula and it's not found on the first line and it is found on the second. How is that possible? The reason for that very often is very tricky, and it's right here. If you double click that and check, it will see there is a space. There is a space that is invisible so visually these two cells are the same, but because there is one space at the end, they will be by accident or for some other reasons who will be fine. So if I delete that space suddenly it works. So be careful with Ilka. Will cover isn't Google. It needs to have an exact match letter by letter character by character toe work Well, so even the empty space can ruin your results. Now let me show you another way how not to do it. So I delete that and try once again. So you look up and I'm looking for that once again in that price list. Once again, I would like to have results from sheet number cold number two and that's it. Percenter. And it seems to work so perfect. I double click it. And once again, it doesn't work on some of the lines. And you're like, Okay, what's wrong in that? What's what's wrong? How is it possible that it doesn't work for some lines and it works for some others? There is no problem. I don't see any problem. The reason is that in this for more, we miss the last input, which asks, if you if you like to do the approximate which match or the exact Mitch, we didn't bullet it if you because in view, look up. If you don't put in, if you would like to put an approximate an exact that's okay because this input is not needed. So that means if you see this brackets this square brackets. That means this input is not needed. And if you don't put it there, Excell automatically choose the approximate match. And because the approximate Majin needs to be alphabetically ordered, it doesn't work and gives you, like, completely nonsense results. The problem is, it doesn't war you. It doesn't tell you that you made something wrong. You just need to know. This is very sneaky things. So don't forget about the zero and the end. And if I put zero, it works. 7. "Numbers stored as text" issue in Excel explained: We have two tables here on the sheet Customers. We have a list off orders and the customer database. The customer database contains customer I D and customer name. And now we need to fill in the customer name into the orders. There will be an easy task with you. Look up functions. So let's do that. So I'm looking up. Uh, this customer I d in that table. And I would like to get the results from coal number two, please. And zero for exact match. Sounds easy. Right? Click. Enter and not available. What does it mean? The Philippine Everything is not available. What is the problem? So, uh, I've seen number five number five, and still it sees its not found. Well, what is that? And what is that thing? Ah, very not available error. That's that's crazy. So So let me explain one thing why I think that happens in excel very often and that you need to know so your life will be much easier. This is text and numbers. There are two different kinds of numbers in the world. Their numbers like money or percentages numbers. You count with numbers servitude, which is some or multiply like like that or like that. That's easy. But there are also other numbers that you don't summon multiply like a full number, for example. Okay, but this time, if I put the phone number in, as you can see that the plus disappeared. Or let's say I can put my employee number right here. And what happens is the zeros on the beginning disappeared. So, uh, what is it? What is the problem here? Because these numbers are not a really numbers. These are more like coats, but Excel tries to convert them into a number for months, and you don't want your full number, you know, to get around it or to get ah, pluses appeared or the zeros on the beginning disappeared. So in order to do it, you need to store these numbers as text. Let me show you how to do it. The easiest way helped us photo store. Let's say phone numbers or any other. Any other ID's that you don't want to be formatted is a number you're just like ever, for example, cellular full column. And I would I say, I would like to input text into these cells so I select next year. And now if I put in the phone number, click en through you will see it will not get round it or the plus sign will know disappear . Same goes for my employee number. I will see this error here. What is it? It says number stored as a text and it's an error. But in this time is actually not an error. Is the correct way how to do it? Because this is not a real number. This is a text. This is a coat and you want to have that start in. Except so in this case, I would say like those and click Ignore error because this is not aerial error. This is the way how things should be. So we go back to our customers. Example. The problem is, I can tell you is that these are numbers started numbers and these are stored Aztecs. So for excel, these are not the same. This is a text and this is the number. It looks the same but from data point of view, these are two different things and the bi look up doesn't see them as the same thing. Luckily, there is a way how to convert text toe numbers and numbers to text 8. How to fix "Number stored as text" problems in VLOOKUP: the situation I'm seeing here on the customer's sheet is right to come on. I have an order list year, and I'm using the Bucha Function toe. Fill in the customer name from the customer database. As you can see, even though the customer number of five clearly does exist in my database, the the formal Loretta Lynn's not available, which means the 80 was not found. How is that possible? The reason in that case is that these customer ID's are stored as the numbers, and these are stored as a text. Numbers and text are two different data types from Excel. They're not the same, and therefore they will not be found or connected with the vehicle function. So in order to be look a function toe work, you need toe have both sides either numbers or text. Luckily, there is a easier way how to convert numbers from text and text to members. I will show you how to convert these two text because it's a customer, I d. It makes sense to store it as a text, so we'll convert that to text as well. Unfortunately, there is no easy way like that. You might think They were just like so, like those And here on the home tap, you just select text. Unfortunately, that doesn't work. You see that the numbers did flip to the left side, but they were not converted to text because what that field says, it's not what is in the cell, but what will be after you put something else in it. So after a neck new entry, if I let's a double click and press enter, which is considered as a new entry, it will be a text item so clear here he will see. Now it's text and now the real cup works. I will go back, but like clicking through all the cells will be really time consuming. So it really use a function to convert these two text I will create and you call him here with control and plus and I will use a faction to convert it. And this function is called text with the here, and this is the value. And the second input off this function is how you would like to format the text. For now, I will use the most easy format, which is like a single zero, which says minimum one digit, which is always the case. So I will use that press enter and it will not work. I will show you how I clicked. Enter in the formula did not calculate it just stayed like text. Why is that is because when you create a column, it will get the properties from the courtroom to the left. And because we select that this should be text next time you put something in there Also, the new column says there should be a text next time you put something in there. And if you have a cell ready for a text input then if you put informed line, it will not be calculated. It will not be executed. It stays like that. So how to fix that? Well, said the full column and switch it toe general text. But General Life like a book, click and click Enter former works now and now This is a text with the book like that. And now everything is converted to text and we're wondering what this other text format can look like. What, you just show you one more people double zero Chris and through Then it says like minimum off two digits. So if it's 505 So there will be good for months, for example, or for other employees numbers. But I can be okay with just seem simple. A single digits. Now I will change this formula. Toe input. This number, right? That. And now you see, it works. But because I don't want to have this special column here, where I can do it is to convert that to from a for more to values. I cannot simply delete the call number one, because then it stops working. So this is not the way how to do it. So in order to do it me to remove the formula here and converted to a simple toe a simple number or actually a text. Copy that, and I will paste it to the same place, but without the formulas, just like it is. So I will use space values and put the values and formatting right here. Okay? And it's against the egg of the warning number stored s text. And yes, this is exactly what I want. So I will click and ignore the error just so I'm not distracted by that. And now I can delete the first column will move that a little bit. I had that as well, and I can delete the court. Okay, so now I saw the problem. I converted the numbers to text. There is also a way how to convert text to numbers. Although in that case I would not recommend it, as I said. But I will show you how to do it anyway. Ah, If I still like that and change it to number, it will not work once again. This one is already for a number input A soon as you put something in there. So if I double click, press enter now it's a number. And now this stops working because this is still a text. But once again, I don't want to click into all the single cell. So that were too much time. I will show you a trick. This is a famous and, uh, long time used tricks off Excell experts how to convert quickly. Ah, quickly text to numbers and it's using a paste function based special functions. I will put the number one right here, and I will use control, see or copy to put it into the clipboard like that. And though I will so like thesis text, I would like to convert numbers Goto paste based, special and click multiply. Okay, what happens is these were converted to numbers. How is that possible? It took this number and I multiplied every single number. Ah, two with a one. You know that multiplication by one does not change the value but the multiplication is counted in excel as a new entry. So basically a forced excel toe convert the text to numbers by just multiplying every single one by one. It is a work around, but it's it works. You can see that one stopped working because now this is This is text. I can still convert this month as well. So I will click based special once again and the multiply Okay. And now these are committed to the two numbers as well. And we look up works again because we had numbers and numbers. So this is how we work with text and numbers in excel. The bag look crazy and it's definitely not intuitive. But as soon as you understand how it works, your life will be so much easier. Especially using veal cup 9. Error handling with IFERROR: let me show you an example here on the if error sheet. This is the order list, and I would like to fill in the trip name for our customers from the price list with we look up some ZZ Leesville Cup, I'm looking at this value. Ah, in the table. First government will be searched. The second column will give me the results, and I would like to get exact match. That's why I put zero percent through, and that's it. Double click here to fill in, and I see some errors here. As you can see, an A. What is that e zero to? Let's check my price list. It's not here. Maybe it's a mistake. Maybe they choose some some trip that doesn't exist. Or maybe my prices is too old, I don't know, but the thing is, this an a error. It does not look very nice. It's actually ugly, and people don't like it. If you send it to your boss and he will find errors in the Excel, he will say, like, What's that? Even though there is a reason for that, because this trip I d does not exist, So how can we replace this error with our own error message, we can use a function which is called if error. And it's about like that if error. So it says like what to do if the regular function gives an error in the can replace you the function so the affair has to imports. The first thing put is the function itself. In this case is a view up. The second input is what should be replaced in case of an error. So I'll put it like that. If error like that you can see this is the value and this is the value if error so quick here, boots and next input. And now what is the value if errors well, would like Toa put if there is an error, I would like to put not found. Are you that following that? Okay, we spell it right? Not found. Okay, so the fight Fill it in Now you will see instead of these mistakes I will have not found which is much nicer than the error. If I would like to have, like just nothing, I can just put things like that. If I put that and a booklet, I will get an empty cell instead of an error, which is much nicer to look at. So this is the for a function. It doesn't work on the way we look up with any other function, and it's quite practical way how tow avoid, ah, error messages. 10. Approximate match with VLOOKUP: if he used. If you look up before, maybe you have only used the exact much You remember that in the fourth input, you always need to put zero or false for the exact match. But what is the approximate match? I have an example for you to show you that here on the approximate much sheet, I have a list of customers and ah, the revenue they make with our company And I have a list off rebate because we have a rebate system for our customers toe incentivize them to spend more with us. So if they reach some level off sales, they will get a rebate. They will get some money back. So ah, in order to unlock some fear, they need to reach the sales. And I would like to find out what is the current rebate tear for each of the companies. So I will use Velicka with approximate match for that because both approximate much does is looks up this value in the table and find the closest smaller value. So I will show you how to do that. You look up. I'm looking up for this value in that table. I would like to get a column, index number two, please. And the range look up this time will be true for approximate match or instead of through a conscious blue one. Or I could just, like skip the 44 input entirely because it's not obligatory. It's not. Even if you don't specify what kind off look up you want, you will get approximate match. So as you can see, this is 15% because 30 million it's just over 10 million and billow 20 millions of 15% is the rebate. It will clear Beth and have filled out in order to get the full rebate. I can just click 15% multiplied by sales, and this is my total rebate. One thing to mention here is that in the surf stable, the search column needs to be sorted from the smallest to the largest value. Otherwise, it will not work this approximate match version off Ville Cup. It's not used very often, and it is only some very few specific cases where it could be useful for you, just as this one, you know, finding the right tear. Very so I hope you enjoyed that one 11. VLOOKUP from multiple source tables: if he used view. Look up before you might have been told that there is only one destination table and one serves stable for the function. I would like to to show you there is a work around toe, have a wee look up and look up from multiple sources from more than one table. I have this example here on the sheet. Multiple sources. I want order list. And I would like to fill in a trip name for a travel agency orders. But the thing is, I have two prices. I have two separate price lists and you know that the trip I d might be in the first prices or it might be in the second. Priceless. So how do I look up in two things? The vehicle function doesn't know how to do it by itself. But we can use the work around to using an if error function. Let me show you how so. First, I will make a function that well, let me let me give you, Let me put it to extract columns here. One will be at we look up, function that well, look up in the first price list. So if you look up. I'm looking at this one in that price list and in coal number two because the trip name and zero for for exact match and let me see what I got. So that works. It can search in the first prices, but this one will be found because e 24 as you can see, is in the second price list. So how can we get that? You know? So I will show you how to get the stuff from the second price list. Puke up looking up this this one in the second price list. And once again come number two and zero for exact match and with me, fill it in. And as you can see, I have the opposite, you know, of course, because they are either in the first price list are in the second price. Still, uh, how to do it. I will use the function if error, if there is a special function and it is used in view, look upto handle errors. For example, if I put if error here, uh, I can put like a message that says not found in the first in the first price list. Okay, look like. But what if instead, off instead of putting that message? What if I put actually the other look up function? So if it's if it takes an error, that means it's not farmed in the first price list. We can do it and find it in the second. Priceless. So I'll copy that function here, and I'll put instead of the error message. So if that be, look up, gives an error because it was not found. Look it up in the other price lists. They look like that, and now everything is found. I don't need that anymore. I would actually copy that basted here to Dili death. And this is how I get the trip name searching two different price lists. You could look up more than two priceless by nesting the affair functions. I would just like if there are, if ever. But then it gets really not very nice for more. So this is just to show that there is a work around for everything in Excel 12. Nested functions with VLOOKUP: did you know that you can use a function inside another function? Let's try that now on nested functions sheet. What I got here is an order list from a travel agency and I need to fill in the name of a country off each trip. As you can guess, the first letter of a trip i d. Is a country coat. And here in the country's list, I have the country coat in the corresponding country name. So I would like to use V look up to find out what is the country name for each off the order. The problem is, I cannot do that because for view, look up. I need to have an exact match. I need to have, like, exactly the same thing in f F 06 It's not in the country code list, so I need to find a way how to separate that. How toe Only use the F to search for that. I'll make an extra kaloum toe separate country coat. Here, let me copy that basted here and how to separate it. Country coat. Uh, for example, we can use the left function. Okay, left. So the left function gives you one character from the left. If you would like to have more than one, you can. You can specify. You can specify more, for example, two or three. But this time we'll just need one. So in that case, we don't have to specify percenter, and you will see I have a country coat F for France, so use the left function toe extract the country code from the trip I d. So now I can use the view cup function toe search for the country name. So I'm looking out for the country coat in the country's list. I would like to get the results from coal number two, and I would like to get exact match. So France is the answer, which is right, and I have all the right answers from the other orders as well. But now can I do it without the country coat? Yes, I can do it s so that the left function is calculated not in an extra column, but it will be calculated inside the View Cup function. Imagine a large fish being the veal cup, the eats, smaller fish being the left function, or a machine inside a machine So instead of linking to a D five cell right here, I will put it directly the contents off the D five cells. Here, let me copy that without the ICO sign just like that. Copy that double click inside here and it's tough. Instead of linking to D five, I will put directly the left function here. So basically, if I read that, it says like look up the first character from the left from C five in that stable. So you are telling them to functions in the one so the inside functional big acquitted first and then it will be looked up in the other tape, So I double click, and that was the same. But because I put the left function already inside of you. Look up. I can delete this extra column and it still works. So this is just like to show you to demonstrate to the principle which is called nested functions or a function without function. You can nest a lot of functions and you can make a very complicated for more with that, and it will save you time and space in your worksheet. 13. Case study: Comparing two price lists using VLOOKUP: well. Cup is a perfect function to compare two tables. For example, I have two sheets here prices this year and prices last year, and I would like to see what is the difference. Which prices changed which products were discontinued and which are the new products. I will use Real Cup to find out all of those. So here in the prices this year, I have this use price and I will look up. What was the price last year using? We look up so price last year for my death and let's look it up. I like to use the insulin function when I'm working across multiple sheets with we look up because it's easier to ever mistakes. So I'm looking out for this item in last use price list, and I would like to get the price just called number two. And of course, I'll put zero for exact match. Okay and priceless Year 700. So is the same for the first item. Let me capital formatting and fill it in for all other gross and now So what was the difference? So let's go equated the friends I'll put like this year minus last year So you see, there is no difference if I double click it. There seems to be no difference in the first few items, so that's sorted. So it's sorted from the smallest. The largest and surprisingly, two of the products got cheaper. 1st 1 by more than 500 euro. That's amazing. So so two products got cheaper. But let's start it from the other way around. And what surprise us. Now it's the errors. There are some products that were not found in the priceless last year there were there, So the reason for that is that these are the new products that were introduced this year. So that's why that there is no difference and no price, and below that you will see products that simply got more expensive. It's a lot of them, so he will see there was a lot off price increase. The last thing you would like to know is which products were discontinued. Uh, how can I find which of the last year's proofs are not in this stable? I need to do the vehicle function, but the other way around. I need to go to the last year stable and look up this year's price and what I see an error that means it's not anymore in the priceless for this year. So price this year. So that's Philip. Four months of you look up once again fuckups I'm looking for Decide them this time I'm looking at in this year's prices and cool number two and range. Look up zero okay and same price, which is good. The book Click and now all sorted from the largest to smallest. And these are products for which the price in this year's braces was not found. That means these are the discontinued product, so comparing to price to price lists or two documents is very important. No, just for ah, priceless in sale, but also in accounting. If you do reconciliation off two systems than using this kind of comparison with the look up is very, very important and very useful. 14. NEW: The XLOOKUP function the future of Excel is here!: Excell Cup is a new amazing function that Microsoft introduced in Excel 3 65 It is there to replace V. Look up. Not only it is easier to use, but it is much more features as well. Let me show you really quick how to use it on this Travel agents example that we used on this training. So X look up has only three inputs on minimum. So first, I'm looking for days. So I'm looking for this trip. I d for Bradley. And this time I will just select the column to search, and I will also select the column Where to get the results. And that's it. No, it out about agrees exact search. You click and that's it and we're done. Wow, isn't that amazing? If you would like to learn more about X, look up, check my other classier and skill share, which is called explica masterclass. Well, I will be there with us for many more years to come because off the older versions. But now is the time to learn the new great way how to look up with explica so check skill share for X look up masterclass 15. Thank you and Wrap-Up: Okay, that's it. And I would know how to use the old cap. Isn't that amazing? If you like Excel, this is a good news, because now we know something new if you don't like excellent much. This is a good news as well, because now you can spend less time with it. Okay, so we look up. It's easy to learn, but it does take some time to master. But now do you know when to use it? And you can always go back to these videos and refresh your knowledge. I wish that this you knowledge this new skills will help you maybe get a new job or get more happier and easier days at your green job. Thank you. And by