-1

Here is the formula I have currently.

This returns one of the header values I need, as there is an "X" located below that header value.

How do I return another header value if there's an "X" value below it?

=INDEX('Sheet1'!$O$5:$U$5,SUMPRODUCT(MAX(('Sheet1'!$O7:$U7="X")*(COLUMN('Sheet1'!$O7:$U7))-COLUMN('Sheet1'!$O$5)+1)))

Headers are located in cells: O5:U5 and values are located in cells: O7:U7

user8421296
  • 1
  • 1

1 Answers1

0

Consider the array formula:

=TEXTJOIN(",",TRUE,IF(O7:Q7="x",O5:Q5,""))

enter image description here

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Do I need a VBA script for this to work? If I'm referencing another sheet, does that make a difference in the formula? I'm getting a #NAME error. – user8421296 Mar 15 '18 at 16:05
  • @user8421296 the **#NAME?** error means that your version of Excel does not include the `TEXTJOIN()` function ....................sorry........... – Gary's Student Mar 15 '18 at 16:45