VLOOKUP: Excel Tutorial

Instructor: Hello everybody, and welcome back. We will go ahead with our case study. In the previous video, we were able to organize all of the line item codes in the same sheet and remove duplicates. Let's apply the VLOOKUP function to our case study. In the sheet 2.1 database, we have the codes of the various P&L line items. Next to each of these codes, we would like to have the P&L account, the partner company number, the name of the partner company and the amount for each of the respective years. One thing you probably noticed is that the column named code is on the right. This will impede us from using the VLOOKUP function immediately. There are two solutions to this problem. One, use another formula or two, modify the source worksheet, let's do the second option. Going back to the FY 2016 sheet, I'll insert an empty column to the left and then cut and paste the column containing the code. This would allow me to perform a VLOOKUP function for the P&L account, partner company and name of partner company. Something I noticed before starting to type the formula is that I'll use it both for the cells below and the cells on the right. Therefore, when typing the first criteria of the formula the lookup value, I will fix only the column references. In this way, When I'm copying the formula downwards I'll be able to select new code criteria and when I copy the formula to the right, it would not go away from the code column. So we have plus VLOOKUP, our lookup value is at B4 with a fixed column reference. We select the table with a range B5 to E61 and fix all of its reference components. Generally speaking, when using VLOOKUP, it is a good practice to fix the references of the source table completely. The P&L account is in the second column of the source table, so we select two as a third argument of the function. The fourth argument will be false because we are looking for an exact match. Now, if we copy the formula for the rest of the code values, we will find the matches it has in the 2016 worksheet. Some of the cells display N/A, but this is understandable as these codes were available only in 2017 and 2018. Let's copy our formula to the right and change the third argument of the formula column index number to three. In this way, it will look for values in the third column of our source. Next, we will have to do the same thing for the column titled name of partner company. Changing the column index number to four. You probably notice these X's I put on the bottom of the page. They allow me to quickly go to the bottom of the data table and paste the formulas I'm working with. I recommend you do the same thing as this allows you to be much faster. We have to deal with all these N/A results. Now, we will have to redo the VLOOKUP function for 2017 and 2018. I will go to the first unavailable result and start from there, selecting the arguments in the exact same way we did for 2016 and fixing the formula references in the same way. Only the column reference is fixed for the lookup value, the whole source table is fixed and then two is the column index number. Let's go to the right, copy and change the column index number to three. Now again, let's go one more time to the right and copy our formula. Changing the column index number to four as we are looking for matches in the fourth column of the source. Pasting these formulas to the bottom, we are able to update the results of all values which we did not have in 2016, but we had in 2017. Now, we need to do the exact same thing for 2018. We go to the source of 2018 data and redo the VLOOKUP formula, starting from the first N/A result. After finishing this exercise, the final result has to be that there are no more not available errors, thanks for watching.