Hello, everyone, this is going to be a practice exercise on V lookup and match something, which helps you take two elements as input and fetch the answer based on a table. Now something very similar we have on our screens, where vertically I have the region names. And horizontally I have the years. Not only that, if I look at the grouped columns, and unfold, one of those will also see the breakup in terms of the four quarters. So for example, 2005, I had a profit of 399 for the North region, and the breakup in terms of four quarters will 93 plus 75, plus 96 plus 135. Now, when I unfold all these columns, what I get to look at is 2003 2004 2005 till 2008.
All these six years, I have the data with proper quarterly break up. Now as a part of a dashboard exercise, what I want as the user selects From the drop down south, or west or east, and based on that the relevant quarter names, he's able to fetch the answer automatically from the given table below. Now, this is a two dimensional lookup why one dimension is from an element given vertically, and one dimension is from an element provided horizontally. And based on the intersection of those two elements, please find the answer. That's what we want. Now, of course, we'll be using VLOOKUP and match and this be a practice exercise.
Let's see how. So we look up. Look for that one value, which most likely you're going to find vertically provided in the main data set. So I'm going to look for not quarter but in this case, north, even if this changes to east or west or south, the still the lookup value logical still hold good. put a comma. Next, Table Array selection, keeping in mind the prerequisite of V lookup.
Let me start from the column which contains the common link. So Shift Ctrl, down, shift control, right? Immediately, I press f4 and comma. Now be aware that I do not have to return back to the old cell where had started writing the formula. Nope, your formula still getting displayed in this function bar that this time, if you press f4, it quickly takes you back there, along with the dollars pleased at the right positions, putting a comma as per our logic column index number This has to be outsourced or delegated to the match formula, who will help us count the position number that is from which column number the answer must be stretched, is it the first second, third, fourth or fifth. Now typically, if you were not aware of match formula is power, you would have counted manually 123, fourth column, fifth column and so on so forth.
And I just wonder that if you have to go to the quarter too often thousand eight Let's see quarter three of 2007 how many columns would you have to start counting? Quite a lot. In fact, you would also not be heat to the fact that some of the columns may be hidden, and the counting may be skipped erroneously, some of the professionals to a slightly better job of not counting manually, instead, they start selecting the cells using shift right right. Now you notice at the bottom right of the selection, the one are multiplied with six, see, that's a sign which is indicating one row selected and six column chosen. This will help you get the count as you go to quarter three of 06. In this case, I'll have to move a little on the right or the 306.
It says one r into 19 C. So I can go back and I can write 19. But the point is, if tomorrow you change this to quarter for 2008, it will not automatically change the count. So what do I do? We will feed in a match formula is our seat. Put a dummy plate Hola ensure that you close the bracket right away. Comma, since you're looking for exact match, and you're not working with a data which is exactly a slab, we will be putting false, or more specifically zero closing the brackets.
Now let's pay full attention to match formula, let's complete this match your work is to focus on the header. And if you have watched our previous videos, you would remember that we had used the analogy of junior follow senior who's the senior formula here, we look up and who's the junior match formula. And match is going to focus just on the header starting from where the SR formula or V lookup has begun. So match lookup value. Look for quarter 306. That's exactly what you're trying to find out in terms of count.
Put a comma lookup array is not a table array. So you cannot you can never choose a table array like this instead, in the lookup array section, start selecting from where we look up had begun. Only for the header. So I'm not choosing before, I'm not choosing later, I'm choosing right from the word region shift control, right? As explained earlier, you can instantly press f4 comma f4 to fix it and comma so that you can finish the match formula with one more final parameter. And that is zero for exact match.
So the very advantage of first reserving a seat for match formula is that you will avoid all kinds of syntax error. Some of the time you may forget to put a bracket here, maybe some of the times you may forget to put zero here, all this could lead to Iranian sensors. Hence, when we look at the formula just to whet whether it's correct or not, all the arrays must be fixed. 104 match one four view cup. That's all let me test it out. Press Enter, I get the answer.
And as I change the quarter, I get the revised answer. For those who are wondering how did I create the drop down validation, please refer to one to flush videos which says drop down list from data validation, and you'll find the mechanism How can you create this drop down list. Until next time a dielectric. See you