CODEWITHSUNDEEP
excelexcel-formula
ggmkp
ggmkp

Reputation: 725

Horizontal Index Match (returning column header)

    A       B       C
1 Fruit   Color   Meat <- Column Header
2 Banana  Red     Pork
3 Apple   Black   Chicken
4 Orange  White   Beef

From the table1 above to table2 below: 

    A       B     
1 Name    What? <- Column Header
2 Banana  Fruit <- Formula should return these values, based on table 1    
3 Red     Color
4 Beef    Meat
5 Pork    Meat

Looking for a formula to return corresponding column name in B2,3,4...
I tried =INDEX(Table1[#Headers],MATCH(J:J,Table1,0))

Upvotes: 0

Views: 2924

Answers (1)

user4039065
user4039065

Reputation:

It would seem that the three columns are unique; e.g. there would never be a Beef in the Color column. In that case you can simply query each column, passing back a 1, 2 or 3 as the case may be.

=IFERROR(INDEX(Table1[#Headers],
               ISNUMBER(MATCH([@Name], Table1[Fruit], 0))*1+
               ISNUMBER(MATCH([@Name], Table1[Color], 0))*2+
               ISNUMBER(MATCH([@Name], Table1[Meat], 0))*3),
       "no column")

    Three column lookup

I'm not sure whether Port was an intentional misspelling but it does demonstrate what occurs when there is no match.

Upvotes: 1

Related Questions