Hello, everybody, hope you enjoy the benefits of V lookup and match. And in fact, if you had gone through our videos on indirect formula, the know now that V lookup match plus indirect makes it a three dimensional lookup. Well, in context of that, let me now add another flavor to this entire lookup strategy that is h lookup with match, although we look up with match will suffice your need. But let me also show you with the same logic lines, how h lookup can be combined with match formula using this example. Now, what I have before me is a sheet from the insurance advisor. And life insurance covered amount is provided in column A.

So all these are hypothetical amounts. It says in case the age is more than or equal to zero, that means up to 25. These are the premium amount you must be for example, if I wish to take a cover of let's say one lap That is one 10th of a million, and my age happens to be 24, then what is the premium I must pay, I would be paying a premium of 1049. Now, in case my age touches 26. In fact, I then should be liable to pay the premium amount in the manner of 1377. So, I shouldn't be able to get an answer immediately automatically in this blank cell.

So one variable is placed horizontally, and that happens to be age and one variable is placed vertically, and that happens to be the life cover the sum insured. So how do I apply h lookup with a match? Well, let me first tell you the basic difference between V lookup and matching h lookup and match. If you remember from our previous discussion, Jr. follows SR strategy. The V lookup covers the entire table and match assists by choosing just the header values. That is a junior Junior follows senior right.

Now, since the lookup requires column index number, hence we applied match in the header. But h lookup requires row index number and that is the reason we will be putting match formula on the first column of the entire table selection. So h lookup will look up for the entire table array. And match will assist h lookup by looking inside this row index number, the first column, keeping that in mind, let me begin writing h lookup with match formula. Let me expand the entire screen. So here it goes.

Equals h lookup, Tab key. HL TAB key will give you the H lookup bracket open. Now lookup value is going to be age is it going to be some insured since it is h lookup it is going to be something which is can be found in the header and that happens to be age, comma, next table array whatever Do just ensure that the first row of your selection must contain the common link of age that is a prerequisite of H lookup. So you cannot choose the data like this, this will be incorrect, the selection must begin from the header which includes the age. So from there, shift control down, I press immediately f4 to freeze it, comma. I'll reserve a seat for match immediately closing a bracket and then comma.

Now question is do I put zero or do I put one since h lookup is looking for 26 and 26 or 29 or 32, whatever that age be, I need an approximate match and remember from our old discussion, if you have a data based on a slab, in ascending order, placed in more than equal to format, you will have to give one and that is what we have done approximate match with the three conditions now Let me go to match what I go to look for, you're going to look for the sum insured. Why? Because that is what I'm going to find in the vertical pleased column. So I look up for the one lakh comma, do I choose from the 550 thousand or some insured? Well, if you remember the junior follow senior principle, you would recommend me that I must choose the self selection from the word some In short, Shift Ctrl down, immediately pressing f4 to freeze it.

Notice the dollar has been placed correctly and comma. Now ask yourself do I put zero or do I put one if you put zero it means exact match. You may be looking for a sum insured let's say one lakh 20,000 Well, there is no one lakh 20,000 out here. So you are looking at a data which is placed in a slab, ascending order and more than equal to hence I'm going to put one Let me press Enter. And let's see what it gives me. So as expected 26 age one lakh cover 1377 And if I change the age to, let's say, 35 steam power, but movement, I put, let's say 36, then notice the premium amount, it's going to turn to 1852.

So that is how it is working with a slab based data, the slab exists in the age as well as the sum insured. And the same logic applied and given that as a solution in the next sheet, so this was h lookup with match. And remember, when you are applying h lookup with match, this is how it is going to look like where the JR is placed on the first column and jr follows Sr. So that was one quick example of H lookup with match. And if you want I can quickly write V lookup with match just beside it. So we look up look for that variable which is given vertically.

So hence I'm choosing not age, which I chosen an H lookup, I'm choosing the sum insured that is the variable given vertically, comma, I start from the center selection first column that is a prerequisite of V lookup, it must contain the common link Shift Ctrl right is the shortcut key which I'm using to choose the entire data pressing f4 comma, reserving a seat for match. And then noticing that the data is in a slab vertically I'll be putting one and match match please look for something that is present horizontally the header. So match look for age 36, comma, were Junior follow senior and I press f4 comma zero. No, it has to be one because this is where the slab base data does exist. Enter. So yes, you are getting the same answer.

Either you apply h lookup with match or V lookup with match. practice those and hope you enjoy it.