Hi. So now that the question is clear that we need to assign appropriate bonus percentage to these employees, based on division, region and reading, let's proceed with the solution. Now we'll be using v lookup match and indirect. And to do that, first I need to name the appropriate arrays, Booth table arrays and lookup arrays, which are going to be referred by V lookup and match formula. So now I'm choosing the first table going to the name box giving a generic name a PAC a back. Now since this is the SR selection, and for those viewers who are finding this new term SR T's effort one of our old videos on V lookup match, you get the idea to which section am I referring to SR selection and to what reference am I referring to jr selection.

So at this moment, I am choosing this particular section and giving this a name EP AC followed by h. h indicates header Under similar strategy I'm going to employ for the next table, I choose the table number to go to name box and write our W and press enter. I choose the table heading. I then name it our who wh please don't I'm using the same naming convention which is what are the name of the endowed table array or SR in our reference, I'm just affixing H or header short form for header towards the end. So as I press enter, towards end of this entire process, I will have four names a pack, a pack H, ro, ro H. Now, let me begin by writing the formula. So we look up, look up for reading. Why so because this is the one variable which you will be able to find in the first column of both the tables from where you're supposed to fetch the answer, comma, table array.

Now, if you are choosing this table physically, you'll notice towards the end of the selection automatically the APAC word comes up. Which means, since the table has been named, we can write a PAC. And this will refer to the table, the first one marked by the red board, comma, this is our sr, the table array is our Sr. Now column index number will be fulfilled by the match formula. Let me write in match formula, press the Tab key and close the bracket immediately following our strategy of putting a dummy placeholder of a formula inside a combo formula, first finishing off the entire framework and then returning back to match formula. At this moment, I put comma and let me check is this data slab? No, it is continuous, right?

So as per our previous discussions on V lookup with two, you must put zero because this data is not a slam. Now, it's a turn off match formula. match your focus is to find the column index number from where the answer must be fetched. So let's put our data HFD lies in the second row. I want match to fetch as to how would you do so, match? Look for the term HIV mm, comma, and where would you start looking for it.

Remember the old strategy of junior follow senior will not take extra cells, he will not take lesser number of cells will start exactly from the boundaries of the SR or V lookup selection. And notice, as soon as I do so, the package names comes up on our screen. So I press comma zero to ensure it's an exact match. And let me test whether this is going to work or not. Hmm Oh yeah, I get the 25% and you can notice h f d rating number three is 25%. If I put rating one changes to 45%, if I change the division name, c d, f d, and I should be getting hundred percent c a b rating one.

But wait. If I change the region to r o w will the answer change should happen. But doesn't, for the simple reason your formula still holds the APAC reference. It is not letting v lookup and match formula to go to the second table for referring to the values. So now here we employ the trick, which is the final part, we will create a desk which will harbored the CIT address CIT of the JR selection. What do I mean by that, you get to know in a minute's time I write equal to I choose the previous cell and I'm putting an ampersand followed by in double quotation, the letter H. This ensures that when I double click at the bottom right of the cell, every one cell in that column has the name of the region followed by H. Now, next trick is I get it inside the main formula.

I did this word a pack. Instead, I am putting in direct formula and following our strategy and closing the bracket right now. Now this indirect formula needs the desk, it is going to the desk, which is now pointing to the five, this is the self containing row. Now imagine what would happen eventually when I press enter in that formula goes to the desk, the desk contains an address cheat row and this means it will be taken to the second tables table array. Similarly, if I go to a package, if I do this portion, and I put in direct, immediately closing the bracket, and this indirect will be pointing to the cell E five, so it's going to the cell containing Row edge and it will be redirected to the root h header. And now you see 110% and that is the answer what we wanted to find out for CDA rating one and the best part if I change this to APAC region, you will get hundred percent.

So there you go. All I have to do is copy paste the remaining and these answers have been now derive using three dimensional lookup, one dimension reading one dimension division name, and the third dimension being the region. So as I ungroup, the entire set of employee list, you'll notice towards the end right till the end, I have been able to calculate the percentage bonus. Now imagine, imagine, just imagine, if you had two more regions, and the table would have looked like this some maybe Latin America or maybe Gulf, the formula length will not change, it will remain the same. All you have to do is simply name the additional tables as per the revised region that Dan shot from for Latin America. And in fact, you will also notice, despite the division name being in the header being different in terms of sequence, the answer comes out correctly, and you can verify the accuracy of this answer.

In fact, before I close this, I'll quickly test it for HIV. effect on the changes reading number three answer changes. So here you go. The concept that we applied was v lookup match indirect