Hi, this super video will talk about two dimensional lookup. And let me tell you if you want to proceed with this exercise, please go through indirect formula naming concept, and V lookup and match. Only in that case we'll be able to extract the best flavor out of this video. So now, let me illustrate the question. So at this moment, I have a group of employee names. And along with that, I have a column dedicated towards their salary, which division that they work for, which region or cost center they have been assigned into.
So APAC refers to Asia Pacific root refers to rest of the world and what rating has they received? Now the question is based on the rating, and the division that they work for, along with the region under which they have been classified, you need to allot them. Bonus percentage in column G. For example, The first person belongs to HFT. And he has received a rating three, and he belongs to a pack a pack means Asia Pacific. So a pack table says HFD rating number three, it should be 25%. Now, you can do this with filter, but imagine applying filter at Tang, why do I say at times because one percentage is based on three variables a pack HFD rating one.
So, if you apply a filter you have to first filter out a pack people within a pack while the HFT guys who have received rating one and you have to do that, how many times at times in this case tell you why. Five ratings a divisions 40 times 40 plus 14 that makes it 80 because the next table refers to the percentage bonus for people who are working under Rule or rest of the world. Now I've seen people they start using if statement But wait, if they move along with a statement, they will definitely have to use an formula. And notice how does it look? And is the rating equal one? Mm hmm.
Is the region equal to a pack? And is the division equal to h, f d. If all these conditions are met, then please give him 45%. And notice the length of the formula that you had to write just to arrive at one answer that is 45%. This is just one of the values from the first table. Imagine, same length of formula you have to write for each and every individual figures. And the length of the formula, as you can imagine, is going to cross several miles.
So we will see in the solution video, how can we achieve this using three dimensional lookup? So as a continuation of the question, we now proceed with the answer. Let me Let's see this strategy. Now when I look at this first table, from where the Some must be fetched and temporarily deleting all the values. What is this reminding you of the structure. This structure where you have one variable placed vertically and one variable placed horizontally This reminds you of forte v lookup and match.
Correct. But the point here is you need to refer to two different tables at different points in time to be able to extract the answer. So now, assume that we just have one table to take care of at this moment. First, we take care of this table and then you see how we apply indirect formula so that this quickly jumps to the next table in case the person belongs to aro. So now I'm writing v lookup formula. Look up for that value which is given vertically, so lookup for not division, not region, it has to be rating, why rating because we are doing a vertical lookup.
Next, table array. Deep Learning, I choose the entire table but ensure that the first column is the one which is containing rating. That was the prerequisite of V lookup. I press f4 comma, next is column index number and this has to be satisfied by whom, by match.