Hi everyone. In this discussion, we'll talk about a very interesting topic in Excel, and something which have been asked repeatedly since last four to five years. And that is reverse lookup. Let me illustrate the problem first. Assume that you are faced with a normal set of displayed data. As you can see from the table one, the vertically pleased data sets are company names, and horizontally placed data is differential parameter.

It'd be a bit that somebody asks you that I want to find the reds and be done. It's a very simple phenomenon of lookup match with which you are able to fetch the answer. Why so because the V lookups prime prerequisite which is the common link must be placed in the first column. Now if I tell you temporarily ignore table one, and focus on table two, now the question remains the same, that you have to fetch Rates EBIT da, based on table number two's placement. And the answer should be 3400. Without a doubt, if you proceed with V lookup, this is how it is going to look like, look up for the value read why because that is placed vertically.

But wait, since the prerequisite of V lookup suggests that you must choose the first column to be the one which contains the common link. Now, if you do so, and you proceed with the normal step of freezing or locking the range f4 comma, question is what column index number would you give, which will help you fetch the answer under the term EBITDA? You cannot give one because that will mean you are referring to company name No. column number two will be fetching the answer from EBIT, you cannot give minus one although the logic says that you have to go to the column prior to the first column to fetch the answer, but minus one is not allowed as column index number seven basically get stuck. And the common The most common step everybody takes is yes, you could have guessed it right that they cut this section, they place the cursor in the first available cell and they say insert cut cells.

So basically they are changing the structure of the data set from where the answer must be fetched. Now, this may not be possible all the time, because you might be working with really complex data and just cutting one column and placing it differently might destabilize the entire Excel model, which may be interlinked. At times the client or the end user may have given you an Excel sheet which is protected. So, with that protected sheet, you cannot simply change the placement of the column as you could do so easily in this particular example. So, the question is, how, how do you do a reverse lookup such that whenever you type in a bit, it gives you the answer based on a bits column and if you write a better Huh, dancer from the left hand side. That's what we want.

So this is reverse lookup and we are going to learn this using a formula called index along with the combination of match formula. So if you have not learned about match formula, I will suggest that you please go through our video on match formula, and then proceed with this particular formula of index. Let's learn more about this mechanism of reverse lookup in the subsequent video.