Multiple If Statement to check for Value?

Justlikebrianw

Board Regular
Joined
Dec 9, 2015
Messages
52
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886
=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

Board Regular
Joined
Dec 9, 2015
Messages
52
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

Well-known Member
Joined
Sep 23, 2005
Messages
886
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

Board Regular
Joined
Dec 9, 2015
Messages
52

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

Well-known Member
Joined
Sep 23, 2005
Messages
886
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

Board Regular
Joined
Dec 9, 2015
Messages
52

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

Well-known Member
Joined
Sep 23, 2005
Messages
886
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

Well-known Member
Joined
Nov 4, 2013
Messages
536
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.

Code:
=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:

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

Hope that helps

Mackers
 

Justlikebrianw

Board Regular
Joined
Dec 9, 2015
Messages
52
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,511
Messages
5,602,079
Members
414,500
Latest member
kevdragon1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top