 Hello, and welcome to the session. This is Professor Farhad. In this session, we would look up the VLOOKUP function in Excel. This topic could be covered in a data analytics for accounting course or introduction to data analytics. As always, I would like to remind you to connect with me only then. If you haven't done so, YouTube is what you would need to subscribe. I have 1,700 plus accounting, auditing, finance, tax, and audit lectures. This is a list of all the courses that I cover. If you like my lectures, please like them, share them, put them in playlists, subscribe. If they benefit you, it means they might benefit other people. Connect with me on Instagram, on my website, farhadlatchers.com. You will find additional resources to supplement your accounting education. What if you are studying for your CPA exam? Basically, help you pass the exam by improving your score by 7 to 10 points. Also, if you're studying for your CMA or EA, I do have resources. To illustrate the concept of the VLOOKUP, we're going to be looking at this fictitious company that has locations in many states. And what's going to happen is they send you this file and you're supposed to prepare the sales tax and submit to the appropriate authority. Notice here you have the sales order ID number, the sales order date when it took place, the sales order quantity sold, how many units sold, product description. It seems we're selling beer here, product selling price, and the store location. And now you are responsible for filling out the state sales tax return for each location. Now you know the state sales tax rate. Here's the record for all the sales state tax rate. For example, Maryland 6%, Minnesota 0.68, so on and so forth. Now, the question is, you could have hundreds of thousands of transactions. Here we have only 150 transactions, but you could have thousands of transactions. So how are you going to match the store location with the appropriate state sales tax? Well, this is where the lookup function will be very, very helpful. So let's go ahead and start to do so. So I'm going to create a new column, call it the state tax rate. So I'm going to put the state tax rate and how am I going to do so? I'm going to use the lookup function. And VLOOKUP, if it's not there, just click on most recently and click on V until you get the VLOOKUP function. Click on OK. And I like to use this because it defines every argument that you're going to be using. First is the lookup value. What is the lookup value? Is the value to be found in the first column of the table and can be a value, a reference, or a tax string? Basically, simply put, the lookup value is what you already know. So what do you want to look up? Think of the lookup value as, let's assume you're going to the library to find a book. You know the name of the book. So the lookup value, it's already known. So the lookup value is already known. What do we know? What are we looking for here? We're looking for the rate. Rate for what? Rate for the state. So we know the states. OK, so that's the case. The lookup value is what do you want to look up? Well, I want to look up the state tax rate. I know the states. So what you do for the first one, you would select the store locations, because this is what you already know. You know this is the information that you're going to be looking for to match with something else. OK, the table array is range. Is a table of tax numbers or a logical value in which data is retrieved. So here, what are you looking for this information? What table range are you looking for? For example, if you have a book, you went to the library, are you looking in the kids' section, the science fiction, the first floor, the second floor, another location? What location are you looking up the information in? Now here, we are looking up the information from our database, which is we already have the state and the rate. So this is where I'm looking up the information. The state and the rate. The third column is the column index number. The column index number is related. So the third argument is related to the second one, the table array. Here, what you're looking for is the number of table array. Notice from which the matching value should be returned. Now you have to be careful here. They're looking for the index number. So here, what we're looking for is we selected, we selected, let me go up here. We selected two columns. This is J and K. Well, they're not called the J and K. This is column one, and this is column two. If we selected J, K, and L, then it's column one, two, and three. If we select the J, K, L, and M, then we have one, two, three, four. So we have to be careful. So what we're looking for is not the state name to match it with the state store location. We want to match the state sale stacks to the store location. Therefore, the column index number is two, not one. I'll go ahead and change it to one later on to show you what would happen. Okay? Now, the range lookup, it's either true or false. Most of the time, you're going to use false. False means you want a 100% match. And in this situation, you want 100% match because you don't want to pay the wrong sale stacks for the wrong state. You'll get a bill or you overpay it for that matter. Why do you put through some time? Let's assume you are auditing payroll and you are looking for a name that could be spelled in more than one way. And you're looking specifically for that name. You may put through, so maybe that name will be pulled out. Okay? But that's beyond the scope of this session. I'm going to click on OK. And what happened here, it matches each state with the appropriate rate, Tennessee, 7%, Texas, 6.25. Let me show you what would happen if I change this argument from two to one in a selected column one, selected column one in the third argument. What would happen is it's going to match each state, so Kansas City location. Yes, it's in Kansas City, Tennessee, Tennessee, so on and so forth. That's not what I'm looking for. I'm looking for the rate. So this is two. Now, once I have this information, I can simply compute my sales taxes. That's easy now for each store location by taking the rate, multiply the rate by the product sale price. And there we go. Now, how much I have to pay sales taxes for each location. Now, that's not very helpful. I want to know for each location specifically. What can I do? Well, guess what? I can use, here's where the pivot table becomes very important or very useful for me. I can go to Insert and let me just click on Insert, Pivot Table. And it's going to go with the table range. It's already noticed it selected my table range. What I'm looking the information, I want to produce this in a new worksheet. Now, if you don't know what I'm doing here in the pivot table, please view my other session. I just wanted to show you how useful is the pivot table because it just thought about this. Click on OK. And here what you're looking for is store location. And you want to know what's the sales tax for each store location. And notice the total I have to pay is 827. For example, I already know I have to play in Oklahoma 106.65, taxes 214.375, so on and so forth. So it's the pivot table becomes very, very helpful in those circumstances. Also, you can click and see all the transactions in taxes if you want to. I hope you find the pivot table helpful because it is very helpful. And notice the data is still there so it doesn't go anywhere. You would use the pivot table. You would use the VLOOKUP. They're very, very powerful Excel function. Make sure you use them at your work. If you like this recording, please like it, share it, subscribe to the channel. If you're an accounting student or a CPA candidate, please visit my website, farhatlectures.com. Stay safe and study hard. Good luck.