VLOOKUP: Excel Tutorial

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 V lookup function to our case study in the sheet, two point one database. We have the codes of the various P and a line items next to each of these codes. We would like to have the Pindel 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 V lookup function immediately. There are two solutions to this problem. One, use another formula or to modify the source worksheet. Let's do the second option, going back to the NY 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 V lookup function for the Pindel 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 we look up, our look up value is at before. With a fixed column reference. We select the table with a range B five to E 61. And fix all of its reference components. Generally speaking, when using the look of it is a good practice to fix the references of the sauce table completely, the initial account is in the second column of the sauce 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 and 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 two 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 two for. You probably noticed these Xs 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 any results now, we will have to redo the V lookup function for 2017 and 2018. I will go to the first unavailable results 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 Look-Up value, the whole source table is fixed and then, too, 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 twenty seventeen. Now we need to do the exact same thing for 2018. We go to the source of 2018 data and redo the V look up formula starting from the 1st and a result. After finishing this exercise, the final result has to be that there are no more not available errors. Thanks for watching.