Excel Reverse Matrix Lookup

Find Column Header based on Matrix Value and Row

Excel reverse lookup problemAre 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.

Free Excel Download

The new Excel Dashboards course is here!

Now available on Udemy

Visually Effective Excel Dashboards for your reports.
Redeem your coupon for 75% off below.

GET 75% OFF THE COURSE
Free Ebook

3 Comments

  1. Tom Cooper July 29, 2017 at 8:54 pm - Reply

    I 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.

  2. Deniz Aksen August 29, 2017 at 3:02 am - Reply

    My 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 Gharani August 31, 2017 at 2:36 pm - Reply

      Nice :) Thank you Deniz for sharing.

Leave A Comment

Share This