Formula assistance

[LauraJackson]

Hi everyone,

I need your expertise with a formula in column F.  Please see attached screenshot.

So far my formula is: =IF(E2="Full CPL","Full","")

I need help with the last part of the formula, the "else".  What I am looking to do is if E2 is not "Full CPL" then look at columns H, I, J, K, L, and M.  If the value equals the word Brand, then display the column name separated by semi colons.

For example, you'll notice on line 4 that E2 is not Full CPL, so I would want it to look at H through M and if the value is the word Brand then display the column name as a value in column F. See second attached screenshot.

Any thoughts on how I can accomplish this in my formula?

[#2 · Accepted solution]

@LauraJackson 

=IF(E2="Full CPL", "Full", TEXTJOIN("; ", TRUE, FILTER($H$1:$M$1, $H2:$M2="Brand", "")))

[#3]

That worked perfectly, thank you so much!

[#4]

@JeremyLloyd 

You're welcome!