Help with IF function scanning a range of cells

[frannyM]

Hello,

I need help with an IF function. I may be using the wrong function, but basically, I want the logical test to scan a range of cells to see if they have "Yes". If the cell has yes, I want to add the column title into the description.

Example: Accessory 1 is compatible with System 1 and System 3. In the description cell, I want to use the formula to scan the row for "Yes" and return System 1, System 3.

When I use this formula =IF(D3:N3="Yes",D2:N2,"") I get a #VALUE! error.

When I use the formula to reference two specific cells, =IF(D7= "Yes",D2,"") it works but then I would have to use multiple formulas in one cell?

I attached a screenshot showing what I am working with, I can clarify further I didn't explain this thoroughly enough.

Thank you!

 

[jasonb75]

Try

=TEXTJOIN(", ",1,IF(D2:N2="Yes",D$1:N$1,""))

Which must be array confirmed with Ctrl Shift Enter.

 

[frannyM]

Try

=TEXTJOIN(", ",1,IF(D2:N2="Yes",D$1:N$1,""))

Which must be array confirmed with Ctrl Shift Enter.

Thanks for the quick response! I tried using that formula it seems to only output 0s. I attached a couple of photos that might be helpful.

 

[jasonb75]

Sorry, my fault. I missed the empty row at the top of the sheet. It should be

=TEXTJOIN(", ",1,IF(D3:N3="Yes",D$2:N$2,""))

 

[frannyM]

Awesome that worked! Thank you for your help much appreciated!

 

[Wisefishman99]

@jasonb75 Hi, I'm trying to apply this to a project. I have the same sheet structure as this example. However, It is returning zeros as franny originally encountered. I'm using this formula as seen in the comments, =TEXTJOIN(", ",1,IF(D3:AF3="Yes",D$1:AF$1,"")). Am I missing something?

 

[Fluff]

Hi & welcome to MrExcel.
Do you have anything in row 1 of the sheet?