I'm sure you'd be fascinated by the trick that I'm about to share with you right now, because I believe this trick is going to help you a lot in preparing, reports, dashboards, and not many other reports, which otherwise would take a lot of time. And I'll start with the semi practical examples, a smaller data set to be able to explain the trick. Now imagine that you have a data set where the vertically placed cells contain all the hypothetical company names. And horizontally placed cells are containing the two items of financial numbers sales and cost. In a simple way, if I were to tell you what is the structure of the data, I would say it's something like this one set vertical, one set horizontal. The intersection of two elements, for example, blue cells, intersection of two elements is what is supposed to give you the answer.
So in this example, I've taken two elements, one is company red, and the second element is sales. I wish To find out what is the sales for the company rate. Now, of course, I can look at the data and tell you right now, Fred says 3400. But I want a formula to fetch this answer for me such that in future, if I write orange in the first yellow cell, in our case study, it quickly changes the answer to 4500. If I change this to cost, automatically, it will bring me 4000. So that is the automation you're trying to look forward.
First, what can help us view ca? Why do you look up? We look up as the name suggests, it will look for one value and that two vertically But wait, if I start writing v lookup, you might have a question. Should I take orange Should I take cost or should I take both? Because as we had discussed, there are two elements which is supposed to set you the answer. If you're thinking of applying both the selection will be incorrect.
Why? Because it says you value it doesn't says lookup values, so I'll have to restrict myself to only one selection. And the second part is amongst these two, which one, orange or cost. Now, since the name suggests v lookup, and in our earlier videos, we also saw that the prerequisite of V lookup is whatever common link or two with which you're trying to connect the data and fetch the answer. That common link or clue must be placed vertically in the first column of your table array. So hence, keeping all this in mind, we will choose orange and not cost, oranges lookup value, comma, table array, I have to keep in mind that the first column of our table or selection must contain the common link and thereafter we can choose the entire block just to make sure the table or it doesn't changes or shift its position IV press f4 f4 to lock D range comma out of the three columns, I must fetch the answer from the third column because I am looking for cost.
So I put in column index number three, comma. Finally, false for exact match. Orange means orange and not red. So I put zero instead of false. That's perfectly acceptable. Enter.
But the difference between the first formula that I wrote right now, and the one that I had written quite some time back, notice the moment I change this to sales, the first one remains static, but the second one automatically gives you sales numbers for orange. So what is the difference? The difference is, I had automated this section of the lookup formula, which is talking about column index number. Since for cost you had given 123. It was a pure and simple count. For sales, you would have counted one and two.
In simple words, you would want delegate this task of counting, outsource this task of counting to another formula, which we have discussed in one of our previous videos. That's called match formula. What is match formula? Let me put a dummy placeholder of match. When I say dummy placeholder, I also refer to the fact that I'm closing the bracket immediately for match formula without having written any parameter inside it. Now, using the loading from our previous videos, match, look up for a world A world, which I want the formula to find and count in the header.
So lookup value sales, comma, it doesn't say table it says lookup array, which means it has to be one dimensional. So you are trying to look for the word sales, starting from where the VLOOKUP formula had started selecting each table array and only choosing the header, not the entire data and certainly not less than what we look up our choosing. And, of course, also not taking more cells than what we look up had chosen. So I make sure the header is chosen. As usual, I'm going to fix this array. I press f4 comma 00 indicates exact match exact match for whom?
Or the word safe. Let me press enter. Let me test it. I put cast on changes, and that's what we want. So what is the match done for you? Matt is trying to look for the word cost.
It then gives you the town One, two and three. Had the word been sales, it would have counted one and two. So in pink simple language matches doing the task for you with respect to counting. We'll see in more depth in the next video, what are the precautions that needs to be taken while writing this combo formula we look up in match