Hello everyone, welcome back to Excel Classroom. Today, we will talk about one of the most commonly used functions in Excel, which is "VLookUp." We will do 10 exercises together. Starting from the basics, step by step, we will practice and familiarize ourselves with the "VLookUp" function. Just like previous videos, in the video description below, you can download today's practice file for free. There will be 10 "VLookUp" exercises. Today, we will first discuss the structure and approach of the "VLookUp" function. During the exercises, I will also introduce some shortcut keys to everyone. You can give them a try. Actually, last year I made two other related videos as well. One introduced a new feature in Excel, the "XLookUp." And the other one compares "VLookUp," "HLookUp," and "XLookUp." After watching this video, you can also watch the other two videos. If this video helps you, please hit the like button. If you have any comments or questions, feel free to leave them in the comments section. If you haven't subscribed to this channel yet, you can also click the subscribe button. I hope that future videos can continue to help everyone. Without further ado, in the video description below, you can download today's practice file. Let's start right away. We are now on Exercise 1 page. Let's first see what the exercise is about. Then we will discuss the structure and approach of the "VLookUp" function. After that, we will complete this exercise step by step. Exercise 1 requires the use of the "VLookUp" function to find the price of an order. The order contains three products with empty prices. The prices can be found in the price list on the right. Once we fill in the prices, we can calculate the total amount for the Pineapple by multiplying the price with the quantity. This is a subtotal, and by adding up all the subtotals, we will get the total. Our first exercise is to do this. The "VLookUp" function may seem complicated at first glance, but once you understand its approach, it becomes less complex. The "VLookUp" function requires four inputs. The first is what you want to find. Then, where you want to search, your search range. After finding this data, what do you want to display, which is the third part. And finally, the search mode, which can be either 0 or 1. 0 is to find an exact match Something identical to what you are looking for It will only show up if it matches exactly Let's not talk about it now; we will discuss it in the next exercise. Even after explaining it this way, you may still not fully understand. So, let's explain it with an actual example. First, let's think about how we normally approach this. For example, now we want to find the price of a Pineapple. Your identifier, which is the first part. What content do you want to find ? Obviously, it's the Pineapple. Pineapple is your identifier. Then, where do you want to search? Where is your data? My data is actually in this price list. Here, because we have the names and prices of all our products, this table will be your search range. It is your second part, your search range. It is your search range. And the third part, what data do you want to display? For example, in this table, if I find Pineapple, for example, we're looking like this now, we want to find Pineapple, we look at each one, oh, we've reached here, we see a Pineapple, there's an exact match here, what will I do now? We want to display what's next to it, meaning the second row, its data. We use an L-shape to look at it. We start scanning down like this, until we find Pineapple, then we look at what's on the right side of it, the content in this column. Its price is 25.9. So, the data I want to display is actually the row number, which is the second row, right? Here is the first, here is the second, so your index, which is the row number to display, is 2. So, the third part of your "VLookUp" function is to enter a 2. We mentioned earlier that there are only two search modes, 0 is for exact match, which is the search mode we are using in this example. Let's not talk about it for now. Now, let's actually write the "VLookUp" function. First, type an equal sign, then type "VLookUp", and open the parentheses. What was the content we were just talking about finding? It's our Gold Pineapple, right? It's in cell A6. So the first step is to select cell A6. Press the comma key. Then, its search range, your search scope. Where do you want your data to be searched? You want to search in this table here. We can open this table. We can press Ctrl+Shift and the direction key to go down. Then press the right key to open the entire table. Because this data range is constant. So we need to add the F4 key, add a lock. We need to add some currency symbols. How do we do that? After you have selected this range, press the F4 key button to lock it. After that, press the comma key again. We want to display which item. Just now, we said the data we want to display is the second item in the table. This is the first, this is the second, right? So type the number 2. Then press the comma key again. We want an exact match, so press 0. We use the search mode 0. Press Enter. We found 25.9. After we have completed this, actually, we can copy cell B6 and then paste the formula. Actually, it will automatically search for others. But because we are currently practicing, let's do it two more times. Let's practice two more times together. Let's do the next two items. We don't want to use autofill. We will type the entire formula. Let's do it one more time. This time, we start with the equal sign and "VLookUp" with an opening parenthesis. This time, what do we want to find? This time, we want to find "Extra Large Fuji Apples." We are in cell A7. Then, press the comma key. Where do we want to search? Our search range is this table. Then, press Ctrl+Shift and the down arrow key. Then, the right arrow key. We have selected that range. Then, press F4 to lock it. Then, press the comma key. We want to display the data from the second row, so press 2. Comma, 0 Closing parenthesis, Enter, done Let's start with the equal sign and "VLookUp" followed by an opening parenthesis. What do we want to find? We want to find "Extra Large Fresh Oranges." Press the comma key. Where do we want to search? The data range is located here. Press F4 to lock it. Comma, press 2. 0 Closing parenthesis, Enter. We have completed the last one. Now, let's calculate the subtotal. Use an equal sign, multiply the price by the quantity. This will give us the subtotal. Let's paste all of these below. Use "Paste Special" and select "Paste Formula." This will calculate the subtotals. Then, for the total, use the SUM function. Add up the three subtotals. This will give us the total. Press Enter. We have completed the first exercise. Now let's move on to the second exercise. The second exercise involves using the "VLookUp" function to help us find the grade for this student. Which grade does their score belong to? If you remember from Exercise 1, we used the search mode 0, which means an exact match. We input 0 for the search mode in the last part. This time, we will use search mode 1. The main purpose of this exercise is to demonstrate what search mode 1 can achieve. Why do we need to use search mode 1? As we mentioned earlier, if you use search mode 0, it requires an exact match. In other words, if I want to find 62, there must be a value of 62 in this table for it to be found. This is the method of exact match. But because this table doesn't have it, as it is a range, for example, from 0 to 50, it will be graded as "F." From 50 to 60, it will be graded as "E." And so on. Therefore, this time we will use search mode 1, which will search within a range. In other words, it will find which range this score belongs to, for example, 62 points is in the range of 0 to 50 or 50 to 60. Or it could be from 60 to 70. Then it will check one by one. Obviously, 62 falls between 60 and 70, so it will stay within this range. And it will display its grade as D. Therefore, if you need to use this search mode, which is mode 1 to search within a range, you must ensure that your data range is sorted in ascending order. If it's not sorted, you won't be able to find which range it belongs to as we did now. So make sure your search range data is sorted in ascending order. Okay, now let's try this method. Let's type "VLOOKUP." And what do we want to find? We want to find this score, which is in cell C6. So we press Comma, and where do we want to search? We want to search within this grading criteria table, which is from F6 to G13. Remember to press F4 to lock it. Which column of data do we want to display? The second column, right there. And for the search mode, this time we won't use 0. Press 1, close the parenthesis, and press Enter. We will find that the grade is D. To give you a chance to practice, we won't use Autofill this time. Although Autofill is usually used, and it's one of the powerful features of VLOOKUP, for practice purposes, we will enter the grading criteria one by one. Let's do it together. Equals sign, "VLOOKUP." What do we want to find? We want to find the score. Look for it here, look for it here. Press F4, Comma, 2, 1, close the parenthesis, Enter. And so on, do the same for the rest. Exercise 3 involves using the VLOOKUP function again to find, for example, the origin of a product, such as where the kiwi is from, the shipping method, and the price. We know where to look, it's this table on the right. As mentioned earlier, We just discussed the basics of "VLOOKUP." If you have confidence, you can pause now and give it a try on your own. Once you're done, come back and check the answer. Now, we will provide the answer and see if you did it correctly. It's evident that we will use an exact match, which is search mode 0. Since we want to find the origin of the kiwi, we will use the zero search mode. Let's take a look at how to construct this table. Press the equal sign, "VLOOKUP." What are we looking for? We want to find that product, which is cell B5, representing the origin of the kiwi. Press the equal sign. Where do we look? Our search range is right here. Press F4 to lock it, and then press the equal sign. What data do we want to display? It is the origin, which is on which row within this data. You will see that the first row represents the product name itself, and the origin is on the second row within this data range. Therefore, our index is 2. Press a comma, and then the search mode is an exact match, which is 0. Close the parentheses, and press Enter. We press the delivery method, "VLOOKUP." What are we looking for? Again, we are looking for the product name. Press a comma. The search range is right here. Press F4 to lock it, and then press a comma. This time, we are looking for the delivery method on which row within this search mode. Actually, it is on the 1st, 2nd, 3rd row, so it is the 3rd row. Therefore, its index is 3. Same search mode, 0. Press Enter. Lastly, we press the equal sign, "VLOOKUP." What are we looking for? We are looking for the product name. The search range is right here. Press F4 and then enter the search mode. The price is on the 1st, 2nd, 3rd, 4th row. It is on the 4th row. The search mode is 0. Close the parentheses and press Enter. We're done. You will see that if you change it to another product, it will automatically update and search for that product's data. Let's move on to the fourth exercise. In the fourth exercise, what do we need to do? Again, we have a data query table on the left side. We want to find, through the student ID, the corresponding data on the right side. For example, this Excel004, which student does this student ID belong to? What is the student's name, gender, score, and the grade that the score belongs to? Our exercise this time is to use "VLOOKUP" to find this data. Again, if you are confident, you can pause now and do it yourself. Once you're done, come back and check the answer. Okay, you're done. Now let's look at the answer together. First, we find the student's name because we already discussed the basics earlier. This time, I'll speak a bit faster. VLOOKUP." What are we looking for? We are looking for the student ID, so it's cell B5. Enter a comma. Where do we look? We look in the adjacent grade records. Remember to press F4 to lock it. Where do we want to display the data? It's right here on the second row. The student's name is on the second row, so your index is 2. We will use an exact match because the student ID must be an exact match for it to display. So the search mode is 0. Close the parentheses and press Enter. Then, for the gender, press the equal sign. "VLOOKUP" open parentheses. What are we looking for? The student ID serves as the identifier. Where do we look? The data range is right here. Remember to press the lock button (F4). Then, for the index, it will be the third row. 1 2 3 So we enter 3. Enter a comma. Search mode: Exact Match, so it's 0. Close the parentheses. Now, for the score, it's the same process. Enter the equal sign, "VLOOKUP" open parentheses. The identifier is the student ID. The range is right here. Press F4 to lock it. The score is on the 1st, 2nd, 3rd, 4th row. It's on the fourth row, so the index is 4. Enter a comma, 0, close the parentheses, and press Enter. Now, we want to find out what grade the score of 82 belongs to. We cannot use an exact match. As mentioned earlier, we need to use the search range. If the search mode is 1. Let's try it together. Enter the equal sign, "VLOOKUP" open parentheses. What do we want to find We need to find the score of 82. So we use cell B8. Select cell B8 and then press a comma. Where is the search range? The search range is below. The grading criteria are here. Press F4 to lock it. Press a comma. Which row does our grade display in? It displays in the 2nd row. So our index is 2. As mentioned earlier, We don't need an exact match. We use search mode 1. Close the parentheses. Press Enter. We're done. Similarly, if you try it, Change it to another student ID. It will automatically search for the relevant data. Now let's move on to exercise 5. It's another common use of "VLOOKUP." Now we want to find out. On the left side, we see a table. It shows what products Store A sells. And the price of each product. On the right side, we see the products and prices that Store B sells. Now I want to know if there are any differences in the products sold by these two stores. If there are any products that Store A sells but Store B doesn't. Similarly, if there are any products that Store B sells but Store A doesn't. We will use "VLOOKUP" to find the answers. Similarly, if you're confident, you can try this question yourself. Try it on your own, and when you're done, come back to see the answer. Now let's see how to solve exercise 5. It's actually very simple. We want to see what Store A has for the "Extra Large Fresh Orange" product. So we use "VLOOKUP" to find it. It's cell A6. We want to check if it appears in E6 to E13. Remember to press F4 to lock it. If it appears, we just need to display its name. If we need an exact match, we press 0, close the parentheses, and press Enter. You will see that it found "Extra Large Fresh Orange" in this table. So it means that Store A sells "Extra Large Fresh Orange," and it's also sold here. Let's copy this formula and apply it to the entire table. We did it too quickly, let's try again. Press Ctrl+C to copy this formula. Then press Ctrl+Down to go here. Then press Ctrl+Shift+Up to select the entire range. Press Alt+E+S to go to Paste Special. Press Formula, press F, and then press Enter. The entire formula will be applied to the entire range. We will see that "Extra Large Fresh Orange" is found, "Apple" is found, "Banana" is found. Huh? It doesn't have "Extra Large Juicy Pear," which means it wants to find "Extra Large Juicy Pear." Turns out there is no table on the right side, which means Store A sells "Extra Large Juicy Pear." But Store B doesn't sell it. Similarly, "Coconut" as well. Similarly, we will use the same method to see what products Store B sells that cannot be found in Store A's product list. We type an equal sign, "VLOOKUP." What is our identifier? It is the name of this product. Where do we search? We search in Store A's product list. Remember to press F4 to lock it, add a comma, and press F4 to lock it. We want to display the name of the product, so we use 1 for exact match, which is 0. Close the parentheses, press Enter, and apply it. That is Alt+E+S+F+Enter. We will see that "Organic Blueberries" appear in Store B but not in Store A. Here we can see that "Extra Large Fuji Apple" is also in Store B's table but cannot be found here. That means Store A does not have it. Actually, Exercise 6 is very similar to Exercise 5. So everyone can pause and try it themselves, then come back to see the answer. What is Exercise 6 about? We want to see what games are in the Xbox game list that PlayStation does not have. Similarly, we want to see what games PlayStation has that Xbox does not have. The method is the same as Exercise 5. Everyone pause now and give it a try, then come back to see the answer. We want to type an equal sign, "VLOOKUP," open parentheses. Look at the game name. Assassin's Creed, we press a comma. We want to see if it appears in the PlayStation list. So the search range is the PlayStation list. Press F4 to lock it. Press Index 1 because we want to display the game name. Then press Exact Match 0. Close the parentheses and press Enter. We apply this formula to the entire table. Press Alt+E+S+F+Enter, which is Formulas, Enter. You will see that the name of Assassin's Creed is found. It also appears in the bottom table. For example, Gear 5, Gear Tactic, Halo. They can't be found in the bottom table. That means these games only appear in the table. They are only available on Xbox and not on PlayStation. Similarly, at the bottom, Sea of Thieves as well, it's only in the top table, not in the bottom. We finish the bottom part. The PlayStation part is the same. Type an equal sign, "VLOOKUP," open parentheses. We want to find this game. If it appears in the Xbox list above. So the search range is the Xbox list above. We press F4 to lock it. We use 1 to represent the game name. The search mode is 0. We press Enter. Copy. Then Alt, E, S, F. Apply this to the entire table. Press Enter. You will see the displayed ones. They appear both above and below. The ones that are not displayed. That means only the bottom table has them. For example, this game is called Deathloop. That means it appears in the bottom table. Only PlayStation sells this game. Xbox does not sell this game. Exercise 7 requires us to combine a function called "IfError" with "VLOOKUP," which is commonly used. When we combine the "IfError" function with "VLOOKUP," what will happen? For example, let's take an example of "VLOOKUP" here. It wants to find data for unit "1C." If it wants to find the area, if I find "1C," it will display 424. But unfortunately, if, for example, this table does not have "1C," for example, now I want to search for "1D." This table does not have "1D." What will it display? Actually, it will display an "error code" (error prompt), which is "#N/A." I don't think it looks good. When I can't find something, if an "error code" (error prompt) appears, "IfError" can help us. If it can't find it, what will it display? You can control it. For example, if I can't find that unit, we can use the "IfError" function to help us display "Property not found." Let's try it out. Once you try it, you will understand better. Let's try it without "IfError" first. We just press the equal sign. "VLOOKUP." Find this unit. Search in the bottom table. We press F4 to lock it. Index is 2. The search mode is 0. Press Enter. We found 1C. If I input a unit that doesn't exist, we will see "#N/A" An "error code" (error prompt) appears. Now, how can we make it not appear? We need to add a function called "IfError." Actually, it's quite simple. Just enter "IfError" before the formula of "VLOOKUP." Start with an opening parenthesis. After that, just put it after the "VLOOKUP" function. Add a comma. For example, if it encounters an error, meaning "VLOOKUP" can't find this identifier, how can we handle it? It should display the phrase we typed, "Property not found." Then close the quotation marks. Close the parenthesis. Press Enter. When I can't find the identifier, the "IfError" function will help me display the sentence "Property not found." Actually, the following steps are all the same. Since this formula is a bit long, let's not type it all out. Let's copy it. Press F2. We will open the entire formula. So we can see it. Press Ctrl+C to copy. Then press Escape. Press B7, this cell. We can paste the entire formula there. This time, we want to search for efficiency. So the display is the third item. We change the index to 3. Press Enter to display. Similarly, "Property not found." Let's adjust a unit that exists first. For example, 1H. Then press F2. We will display the entire formula. Copy it. Press B8, this cell. It will display index 4. So we change it to 4. Actually, that's all we need to do. Afterward, we press F2 to paste. Change the index to 5. And it's the same. Change this one to 6. Finally, the rent is the seventh item. Change the index to 7. And we have completed the entire table. Practice 8 is also a common approach. Basically, it's about finding some data. What are we going to do? This time, we use the "VLOOKUP" function. Let's see. We want to know the performance of our investment portfolio each year. You will see that our data is messy. For example, in 2018, the Hong Kong bond fund is listed above, and there are many different funds below. The sorting is a bit different. So if we search manually, it will take a long time to find. It has different years. We can quickly complete this task using the "VLOOKUP" function. Everyone has learned about "VLOOKUP" before. So let's pause here. Try it yourself and see if you can do it. Then continue watching this video for the answer. Alright, let's start with the answer. We go to cell B6. Let's start with the year 2021. We'll start from 2021. Type an equal sign, "VLOOKUP," open parenthesis. We look for the name of the fund, the identifier, cell A6. Type a comma. Where do we want to search? It's the performance of this fund in 2021. The search range is here. Remember to press F4 to lock it, then type a comma. We want to display the second row because the performance is in the second row. Type 2 for the index. Exact Match, type 0. Press Enter. Press Ctrl+C to copy. Then press Ctrl and the down arrow, then the right arrow. Then press Ctrl+Shift and the up arrow. Then press Alt, E, S, F. We paste this performance into the cell for 2021. The process for 2020 is basically the same. Let's do it together. Type an equal sign, "VLOOKUP," open parenthesis. What are we looking for? The name of the fund. The identifier is A6, comma. The data range for 2020 is here. Press F4 to lock it, and don't forget to index 2. The search mode is 0. Close parentheses, Enter. Ctrl+C, then press Alt, E, S, F. Paste it in the range below. For the year 2019, type an equal sign and "VLOOKUP." A6 is also the identifier. The data for 2019 is here. Press F4 to lock it. The index is 2. The search mode is 0. Close parentheses, Enter. Ctrl+C, Alt, E, S, F, paste. For the year 2018, type an equal sign and "VLOOKUP." A6 is the identifier. The search range is for 2018. Press F4 to lock it. Then the index is 2. The search mode is 0. Close parentheses, Enter. Ctrl+C, Alt, E, S, F, paste. It's done. Now let's move on to Practice 9. This one can also be considered a challenging question. Because it will be a bit more difficult. There's a little bit to think about. Let's see what we need to do. We need to use the "VLOOKUP" function along with the date function. Now let's retrieve the zodiac sign from the zodiac list based on the birthdate. What's the difficulty? The difficulty is that on this list, the year may not necessarily match yours. Because this list doesn't have 1990, so we need to use the date function. Similarly, everyone can try this challenge. Pause the video, try it yourself, and then come back to see the answer. Now let's look at the answer together. First step, you need to look at the zodiac list. Actually, all dates should have the same year. First, make sure that everything here is based on the year 2022. Then we need to use the date function. First, we ignore the year 1990 or any other year. Make all the dates have the same standard year. For example, let's use 2022. It may sound complicated, but I'll try it once to show you, and you'll understand what I mean. Let's start with this equal sign. VLOOKUP. Now, the LOOKUP VALUE, what is its identifier? We will use the DATE function. Open parentheses. Then we will use the value "2022" for the year. We will change the year to 2022. We will use the month with the MONTH function, open parentheses. Then use the value of A6 to get the month. Similarly, for the day, use the DAY function, open parentheses. We will use the day from A6. We open parentheses, then another set of parentheses, and use the DATE function. This is the identifier for VLOOKUP. Press a comma. The second step of VLOOKUP is the search range. The search range is here. It's this zodiac list. Remember to press F4 to lock it, then press a comma. Our index is 2, to display the second row. And the search mode. We are not doing an exact match, we want to search the range. So it's not 0, we'll use 1. The search mode is 1. So the fourth part is 1. Then close parentheses and press Enter. It's done. You will find that the zodiac sign for the birthdate is Taurus. Now we come to the last exercise. The last exercise is, of course, a more challenging one. We will use the VLOOKUP to find the zodiac sign based on the birth year. What is your zodiac sign? What is your zodiac sign? The difficulty is similar to the previous Exercise 9. You cannot list out every year. So how do we do it? Everyone, pause the video. Try to think about how to do it. After you finish, continue watching the rest of the video. And see the final answer. Now let's see the answer to Exercise 10 together. We will use the MOD function. It can calculate the remainder of a number. We will calculate the remainder of the birth year divided by 12. Then we will calculate the remainder of each zodiac year. And based on the remainder, we will match it with your birth year to find the zodiac sign. It may sound complicated when explained like this. But once we do it, you will understand. In cell C6, type an equal sign. MOD, open parentheses. We want to calculate the remainder of the birth year divided by the Year of the Monkey. We need to calculate for the year 1992, which is in cell E6. Type an equal sign and divide it by 12. Close parentheses. It turns out that the birth years in the following year all have a remainder of 0. We will paste this formula below as well. Press Alt, E, S, F, Enter. You will calculate the remainder when dividing each zodiac year by 12. Then we can perform our "VLOOKUP". Type an equal sign, "VLOOKUP", open parentheses. This time we will look at cell A6. What is the remainder for the year 1990? Press MOD, open parentheses. Select cell A6, divide it by 12. Close parentheses. Press a comma. This is our LOOKUP value. Press MOD, cell A6 divided by 12, which is our LOOKUP value. Then, where is our search range? It is in column C to column D, which is the list we just created. Look for what zodiac sign the remainder represents. Remember to press F4 to lock, and the second part is done. Press a comma. The third part will display the content of the second row. So the index is 2. This time we want an exact match because we want to find the zodiac sign that exactly matches the remainder. So we will use a search mode of 0. Close parentheses and press Enter. We will see that the year 1990 is the Year of the Horse. Let's try another one, like the year 1997. 1997 is the Year of the Ox, correct. This table is complete. This is also our last exercise, the answer to Exercise 10. Today we have completed 10 exercises on VLOOKUP. I believe by now you should be familiar with the VLOOKUP function. If this video has been helpful, please click the LIKE button and subscribe to my channel. I hope to continue helping you with future videos. You can check out my related videos from the past year. The links are here, you can click on them to watch. That's it for today. See you in the next video. Goodbye.
