## Are you familiar with INDEX MATCH?

If you’re familiar with Index and Match you know how to solve a matrix lookup problem. You want to find a value inside the matrix by looking up the row and column headers.

What happens though when it’s reversed? You want to find the column header based on the value inside the matrix as well as the row header.

To solve this Excel problem, we will take a look at INDEX & MATCH and use two sets of INDEX MATCH to get our result. We will actually use the row argument of index inside the column argument to help us correct the range for the match function. Sounds confusing? It’s actually quite simple;

### Watch the steps in this video:

Feel free to Download the Workbook HERE.

Tom CooperJuly 29, 2017 at 8:54 pmI just found this website yesterday. It is the best advanced Excel site and associated Udemy course that I have ever seen.

Thank you so much for providing do much great info in a visually rewarding manner.

Deniz AksenAugust 29, 2017 at 3:02 amMy solution would be slightly different, Leila.

FIRST:

i-) Select with your mouse the range A4:G6 and then either press the shortcut CTRL+SHIFT+F3 or go to the “Defined Names” group in the “FORMULAS” ribbon in your Excel menu bar.

ii-) Tell Excel that you want to “Create Names From Selection” where names are to be created from values in the “Left column” only.

Presto! You have now AUTOMATICALLY created the name “Product_1” for the row vector range B4:G4, the name “Product_2” for the row vector range B5:G5, and finally the name “Product_3″ for the row vector range B6:G6.

THEN:

Write this formula and enter it as a simple formula –> =INDEX(B3:G3,MATCH(B10,INDIRECT(SUBSTITUTE(B9,” “,”_”)),0))

Vola!

Note:

Had there been no blank spaces inside the product names, we wouldn’t have to use the SUBSTITUTE() function. In that case, we would simply write INDIRECT(B9). However, since blank spaces in a cell value are automatically replaced by the underscore character “_” when a named range is created with that value as its name, we should do the same substitution before calling the name inside an INDIRECT() function.

Cheers’n best regards! — Deniz

Leila GharaniAugust 31, 2017 at 2:36 pmNice :) Thank you Deniz for sharing.

MAHBUBOctober 28, 2017 at 9:32 pmThanks a lot Deniz for your solution. But as a ordinary user it seemed to me much critical to understand. Your formula seems much complicated and several additional steps are to be taken to get the result like Defining Name, SUBSTITUTE(), INDIRECT().

The most important is that , Deniz’s formula will not work for the following two situation, where Leila’s formula will be effective:

a. The names are not dynamic! If I need to change the name of the product for example ‘Product 1’ is renamed as ‘Product 1 Small’ then the Defined Name will NOT work.

b. If we delete any of the product (or if the order of the product sequence are changed e.g. Product 2, Product 1) then also Deniz’s formula will NOT work.

But in both the situation Leila’s formula will work fine. It is time saving, easy and most importantly dynamic.

Thanks to both of you for sharing.

Regards

MD MAHBUB HOSSAIN

Leila GharaniOctober 30, 2017 at 12:44 pmThank you Mahbub for the detailed analysis.

MAHBUBOctober 30, 2017 at 11:05 pmMy pleasure Leila!

And thank you once again for such nice tutorial !

I wish I could join your Excel courses!!!

rossmcApril 19, 2018 at 12:03 amAlmost what I want, in this example I need to return the Product from the reverse match (row), not the Category (column). Cannot figure it out, any help would be much appreciated

NickSeptember 27, 2018 at 5:46 pmrossmc, I don’t know if you’re still looking for that solution, but I was as well when I stumbled upon this. It took me forever to wrap my brain around how to pseudo reverse the process she describes above, but this same thing works looking for rows. Using the example she gives, This should work.

NOTE: This formula uses B11, which in the video above is what she uses to find the category. Of course you could also just add a cell that has the category you are specifically looking for somewhere else on the sheet, but I did this to keep it related so you could just pop this formula into cell B12 (which I did to test this) and it works from what you can download just below the video..

=INDEX(A4:G6,MATCH(B10,INDEX(B4:G6,,MATCH(B11,B3:G3,0)),0),1)

Takes some mental back flips to not only adjust the matches and variables you’re looking for, but also the null sections of the formula so variables work.

Azeem InamdarAugust 2, 2019 at 9:41 amMs. Leila Gharani,

Many thanks for your all the videos. Madam, I have more or less same problem; please advice.

please take your same data the only diffrence is that I dont have Product column, my Categori1, Category2 ………having different but unique ITEM NAMES below them.

I want to search exact Item name from Column B4:G6 in Column A1 (Description: it will be long text in that any item names will be there) if any item found in Column A then Display its Heading same with Column A2, Column A3….

Hope I explain correctly. Please if you have any quries, let me know.

Thank you very much in Advance

ChrisAugust 23, 2019 at 6:57 pmHi – best would be to post your specific question with screenshots on an Excel forum – for example Microsoft’s tech community right here.

The Excel Tech Community has some of the finest minds in the industry. No matter your issue, I’m certain someone there can inform you of the best way to reach your solution.