0

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

Improve this question

2 Answers 2

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I have just tried on a real use case and it looks like the formula you provided ajusted to that use case returns an error. I have a Google Spreadsheet test if that helps. Based on a value on a dropdown menu (Select Country) in one sheet (Check), I am trying to get values (Domain, Start Date, Status) from another sheet (List) where all the data is. Would you mind having a look: docs.google.com/a/cjorge-consulting.com/spreadsheets/d/…
I have updated the question. Let me know if that is clearer.
0

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)))))
Improve this answer

3 Comments

Hi @a.celiwa, many thanks for this formula. I have updated the formula to be able to search on the cell A7 (instead of A8). It works brilliantly! =INDEX($A$2:$A$4,MATCH("Yes",INDIRECT(ADDRESS(2,MATCH($A$7,$A$1:$D$1))&":"&ADDRESS(4,MATCH($A$7,$A$1:$D$1)))))
Hi @a.celiwa, I have just tried on a real use case and it looks like the formula you provided ajusted to that use case returns the wrong information. I have a Google Spreadsheet test if that helps. Based on a value on a dropdown menu (Select Country) in one sheet (Check), I am trying to get values (Domain, Start Date, Status) from another sheet (List) where all the data is. Would you mind having a look: docs.google.com/a/cjorge-consulting.com/spreadsheets/d/…
I have updated the question. Let me know if that is clearer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.