Excel - Return row header after searching for column header and a specific value in that column

0
votes

I would like an Excel formula that returns the column header (value in column A) based on 2 conditions:

  • Header Column Name (Country 1 in the example below)
  • Specific Value in that Column (Yes in the example below) So in this case, If my Search Cell (A12) has the value "Country 1", I will get the result "Domain 5" in the Result Cell (B12).

As well, I should be able to add columns (yellow headers) without breaking the formula.

Full result table below:

  • Country 1: Domain 1
  • Country 2: Domain 3
  • Country 3: Domain 2
  • Country 4: Domain 6
  • Country 5: Domain 4
  • Country 6: Domain 5
  • Country 7: Domain 8
  • Country 8: Domain 7

I have created a google spreadsheet below with the example on the tab "Example". What I am trying to achieve is visible on the tab "Check". https://docs.google.com/spreadsheets/d/1FjHkCs8MoFy6w-mhcyufD1VEBX-r3Rdc98GoNcsKeC0/edit?usp=sharing

I would really appreciate if someone could give me a hand.

Simplified example

2
excel-formulalookup

2 Answers

1
votes

It's a double MATCH with an INDEX and an OFFSET:

=INDEX($A$2:$A$4,MATCH("Yes",OFFSET($A$2:$A$4,0, MATCH(A7,$B$1:$D$1,0)),0))

enter image description here

0
votes

In cell B7 use this formula:

=INDEX($A$2:$A$4,MATCH("Yes",INDIRECT(ADDRESS(2,MATCH($A$8,$A$1:$D$1))&":"&ADDRESS(4,MATCH($A$8,$A$1:$D$1)))))