Multiple If Statement to check for Value?

[Justlikebrianw]

I have 14 column headers in row B

Under each column header I have an X under them, or blank

How do I concatenate in a cell using an If statement? I am currently getting an error right now

=IF(EF3 = "X", EF2, ""), IF(EG3 = "X", EG2, "") etc..

After the second IF STATEMENT I get an error?

How can I do this?

Thanks

 

[Airfix9]

=IF(EF3="X",EF2,IF(EG3="X",EG2,""))

It is Test, True, False for each IF statement. If the value of EF3="X" is false, it should go to the next IF statement.

 

[Justlikebrianw]

Hello Airfix

I guess the problem is in another cell I am trying to get the column header of all the values where there is an X in EF3 or EG3 etc..

Unfortunately it looks like it's only grabbing the ef2 now and not eg2 if there is an X

 

[Airfix9]

OK, so there could be an X in either or both and, if so, you want the header? If there is an X in both, which header do you want? IF is a logical progression formula, once it resolves to TRUE, it considers its work done and returns the TRUE value.

Assuming that the headers are in EF2 and EG2, what is the logical requirement?

 

[Justlikebrianw]

ADVERTISEMENT

Hi Airfix -

Thanks again for help, you are correct and sorry for delayed response (car broke down)

You're correct, if there is an X under say both headers, I want them both to show up in this other cell.. Any other suggestions on how to do this? Basically, if there is an X in the row under that column with an X, I want to pull the column header name.

Example. Entity ID, Name, IT Incident
X , , X

In the below example, I would want in another cell to say "Entity ID IT INCIDENT" or something a long those lines.. If I could make that into a drop down even better.

 

[Airfix9]

Sorry to hear about the car, hope it's something simple. Excel I can do but carburetors defeat me!

How many columns would have a X in them? Which columns are they? Sorry to ask but it seemed to be two to start with and now it seems to be three (Entity ID, Name and IT Incident)

 

[Justlikebrianw]

ADVERTISEMENT

Hi,

So I have 16 column headers

Under each column header someone can put an X under each one to represent if that applies to them

Say for example the 16 headers were a food item
Rows under would be different customers and their food selection - they would indicate by putting an "X" under the column header

So in the cell after the 16'th column header, I am trying to get what they selected. So if they put an X under the first 3 columns and the 16'th, I could see what food items they selected based on where they put the "X".

 

[Airfix9]

Like this?

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Name</td><td style=";">Food 1</td><td style=";">Food 2</td><td style=";">Food 3</td><td style=";">Food 4</td><td style=";">Food 5</td><td style=";">Food 6</td><td style=";">Food 7</td><td style=";">Food 8</td><td style=";">Food 9</td><td style=";">Food 10</td><td style=";">Food 11</td><td style=";">Food 12</td><td style=";">Food 13</td><td style=";">Food 14</td><td style=";">Food 15</td><td style=";">Food 16</td><td style=";">Order</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Alan</td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style=";">Food 2Food 5Food 9</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Brian</td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style=";">Food 1Food 4Food 8</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Charlie</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style=";">Food 5Food 9Food 12Food 15</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Dave</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style=";">Food 3Food 7Food 11Food 16</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Eric</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style=";">Food 4Food 7Food 11Food 15</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Frank</td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style=";">Food 1Food 4Food 9Food 13</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">George</td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style=";">Food 2Food 6Food 10Food 14</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Hugh</td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style=";">Food 1Food 3Food 8</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">R2</th><td style="text-align:left">=IF(<font color="Blue">B2="X",B$1,""</font>)&IF(<font color="Blue">C2="X",C$1,""</font>)&IF(<font color="Blue">D2="X",D$1,""</font>)&IF(<font color="Blue">E2="X",E$1,""</font>)&IF(<font color="Blue">F2="X",F$1,""</font>)&IF(<font color="Blue">G2="X",G$1,""</font>)&IF(<font color="Blue">H2="X",H$1,""</font>)&IF(<font color="Blue">I2="X",I$1,""</font>)&IF(<font color="Blue">J2="X",J$1,""</font>)&IF(<font color="Blue">K2="X",K$1,""</font>)&IF(<font color="Blue">L2="X",L$1,""</font>)&IF(<font color="Blue">M2="X",M$1,""</font>)&IF(<font color="Blue">N2="X",N$1,""</font>)&IF(<font color="Blue">O2="X",O$1,""</font>)&IF(<font color="Blue">P2="X",P$1,""</font>)&IF(<font color="Blue">Q2="X",Q$1,""</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">R3</th><td style="text-align:left">=IF(<font color="Blue">B3="X",B$1,""</font>)&IF(<font color="Blue">C3="X",C$1,""</font>)&IF(<font color="Blue">D3="X",D$1,""</font>)&IF(<font color="Blue">E3="X",E$1,""</font>)&IF(<font color="Blue">F3="X",F$1,""</font>)&IF(<font color="Blue">G3="X",G$1,""</font>)&IF(<font color="Blue">H3="X",H$1,""</font>)&IF(<font color="Blue">I3="X",I$1,""</font>)&IF(<font color="Blue">J3="X",J$1,""</font>)&IF(<font color="Blue">K3="X",K$1,""</font>)&IF(<font color="Blue">L3="X",L$1,""</font>)&IF(<font color="Blue">M3="X",M$1,""</font>)&IF(<font color="Blue">N3="X",N$1,""</font>)&IF(<font color="Blue">O3="X",O$1,""</font>)&IF(<font color="Blue">P3="X",P$1,""</font>)&IF(<font color="Blue">Q3="X",Q$1,""</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">R4</th><td style="text-align:left">=IF(<font color="Blue">B4="X",B$1,""</font>)&IF(<font color="Blue">C4="X",C$1,""</font>)&IF(<font color="Blue">D4="X",D$1,""</font>)&IF(<font color="Blue">E4="X",E$1,""</font>)&IF(<font color="Blue">F4="X",F$1,""</font>)&IF(<font color="Blue">G4="X",G$1,""</font>)&IF(<font color="Blue">H4="X",H$1,""</font>)&IF(<font color="Blue">I4="X",I$1,""</font>)&IF(<font color="Blue">J4="X",J$1,""</font>)&IF(<font color="Blue">K4="X",K$1,""</font>)&IF(<font color="Blue">L4="X",L$1,""</font>)&IF(<font color="Blue">M4="X",M$1,""</font>)&IF(<font color="Blue">N4="X",N$1,""</font>)&IF(<font color="Blue">O4="X",O$1,""</font>)&IF(<font color="Blue">P4="X",P$1,""</font>)&IF(<font color="Blue">Q4="X",Q$1,""</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">R5</th><td style="text-align:left">=IF(<font color="Blue">B5="X",B$1,""</font>)&IF(<font color="Blue">C5="X",C$1,""</font>)&IF(<font color="Blue">D5="X",D$1,""</font>)&IF(<font color="Blue">E5="X",E$1,""</font>)&IF(<font color="Blue">F5="X",F$1,""</font>)&IF(<font color="Blue">G5="X",G$1,""</font>)&IF(<font color="Blue">H5="X",H$1,""</font>)&IF(<font color="Blue">I5="X",I$1,""</font>)&IF(<font color="Blue">J5="X",J$1,""</font>)&IF(<font color="Blue">K5="X",K$1,""</font>)&IF(<font color="Blue">L5="X",L$1,""</font>)&IF(<font color="Blue">M5="X",M$1,""</font>)&IF(<font color="Blue">N5="X",N$1,""</font>)&IF(<font color="Blue">O5="X",O$1,""</font>)&IF(<font color="Blue">P5="X",P$1,""</font>)&IF(<font color="Blue">Q5="X",Q$1,""</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">R6</th><td style="text-align:left">=IF(<font color="Blue">B6="X",B$1,""</font>)&IF(<font color="Blue">C6="X",C$1,""</font>)&IF(<font color="Blue">D6="X",D$1,""</font>)&IF(<font color="Blue">E6="X",E$1,""</font>)&IF(<font color="Blue">F6="X",F$1,""</font>)&IF(<font color="Blue">G6="X",G$1,""</font>)&IF(<font color="Blue">H6="X",H$1,""</font>)&IF(<font color="Blue">I6="X",I$1,""</font>)&IF(<font color="Blue">J6="X",J$1,""</font>)&IF(<font color="Blue">K6="X",K$1,""</font>)&IF(<font color="Blue">L6="X",L$1,""</font>)&IF(<font color="Blue">M6="X",M$1,""</font>)&IF(<font color="Blue">N6="X",N$1,""</font>)&IF(<font color="Blue">O6="X",O$1,""</font>)&IF(<font color="Blue">P6="X",P$1,""</font>)&IF(<font color="Blue">Q6="X",Q$1,""</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">R7</th><td style="text-align:left">=IF(<font color="Blue">B7="X",B$1,""</font>)&IF(<font color="Blue">C7="X",C$1,""</font>)&IF(<font color="Blue">D7="X",D$1,""</font>)&IF(<font color="Blue">E7="X",E$1,""</font>)&IF(<font color="Blue">F7="X",F$1,""</font>)&IF(<font color="Blue">G7="X",G$1,""</font>)&IF(<font color="Blue">H7="X",H$1,""</font>)&IF(<font color="Blue">I7="X",I$1,""</font>)&IF(<font color="Blue">J7="X",J$1,""</font>)&IF(<font color="Blue">K7="X",K$1,""</font>)&IF(<font color="Blue">L7="X",L$1,""</font>)&IF(<font color="Blue">M7="X",M$1,""</font>)&IF(<font color="Blue">N7="X",N$1,""</font>)&IF(<font color="Blue">O7="X",O$1,""</font>)&IF(<font color="Blue">P7="X",P$1,""</font>)&IF(<font color="Blue">Q7="X",Q$1,""</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">R8</th><td style="text-align:left">=IF(<font color="Blue">B8="X",B$1,""</font>)&IF(<font color="Blue">C8="X",C$1,""</font>)&IF(<font color="Blue">D8="X",D$1,""</font>)&IF(<font color="Blue">E8="X",E$1,""</font>)&IF(<font color="Blue">F8="X",F$1,""</font>)&IF(<font color="Blue">G8="X",G$1,""</font>)&IF(<font color="Blue">H8="X",H$1,""</font>)&IF(<font color="Blue">I8="X",I$1,""</font>)&IF(<font color="Blue">J8="X",J$1,""</font>)&IF(<font color="Blue">K8="X",K$1,""</font>)&IF(<font color="Blue">L8="X",L$1,""</font>)&IF(<font color="Blue">M8="X",M$1,""</font>)&IF(<font color="Blue">N8="X",N$1,""</font>)&IF(<font color="Blue">O8="X",O$1,""</font>)&IF(<font color="Blue">P8="X",P$1,""</font>)&IF(<font color="Blue">Q8="X",Q$1,""</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">R9</th><td style="text-align:left">=IF(<font color="Blue">B9="X",B$1,""</font>)&IF(<font color="Blue">C9="X",C$1,""</font>)&IF(<font color="Blue">D9="X",D$1,""</font>)&IF(<font color="Blue">E9="X",E$1,""</font>)&IF(<font color="Blue">F9="X",F$1,""</font>)&IF(<font color="Blue">G9="X",G$1,""</font>)&IF(<font color="Blue">H9="X",H$1,""</font>)&IF(<font color="Blue">I9="X",I$1,""</font>)&IF(<font color="Blue">J9="X",J$1,""</font>)&IF(<font color="Blue">K9="X",K$1,""</font>)&IF(<font color="Blue">L9="X",L$1,""</font>)&IF(<font color="Blue">M9="X",M$1,""</font>)&IF(<font color="Blue">N9="X",N$1,""</font>)&IF(<font color="Blue">O9="X",O$1,""</font>)&IF(<font color="Blue">P9="X",P$1,""</font>)&IF(<font color="Blue">Q9="X",Q$1,""</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Mackers]

Hi

What you want is ampersands rather than commas between the IF() statements to stick them all together. There is probably a more elegant way of doing it but this at least answers your original question.

=IF(EF3 = "X", EF2, "")[COLOR="#FF0000"]&[/COLOR]IF(EG3 = "X", EG2, "")

To make it prettier you probably want to add spaces before and after the cells (so EF2 would be replaced with " "&EF2&" ") and then apply the TRIM() function across the whole thing:

=TRIM(IF(EF3 = "X", " "&EF2&" ", "")[COLOR="#FF0000"]&[/COLOR]IF(EG3 = "X", " "&EG2&" ", ""))

Hope that helps

Mackers

 

[Justlikebrianw]

You guys are literally the best!!! I can't thank you enough for taking the time to help out. I may have another question on this, but wanted to thank