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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

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?


Excel 2012
ABCDEFGHIJKLMNOPQR
1NameFood 1Food 2Food 3Food 4Food 5Food 6Food 7Food 8Food 9Food 10Food 11Food 12Food 13Food 14Food 15Food 16Order
2AlanXXXFood 2Food 5Food 9
3BrianXXXFood 1Food 4Food 8
4CharlieXXXXFood 5Food 9Food 12Food 15
5DaveXXXXFood 3Food 7Food 11Food 16
6EricXXXXFood 4Food 7Food 11Food 15
7FrankXXXXFood 1Food 4Food 9Food 13
8GeorgeXXXXFood 2Food 6Food 10Food 14
9HughXXXFood 1Food 3Food 8
Sheet1
Cell Formulas
RangeFormula
R2=IF(B2="X",B$1,"")&IF(C2="X",C$1,"")&IF(D2="X",D$1,"")&IF(E2="X",E$1,"")&IF(F2="X",F$1,"")&IF(G2="X",G$1,"")&IF(H2="X",H$1,"")&IF(I2="X",I$1,"")&IF(J2="X",J$1,"")&IF(K2="X",K$1,"")&IF(L2="X",L$1,"")&IF(M2="X",M$1,"")&IF(N2="X",N$1,"")&IF(O2="X",O$1,"")&IF(P2="X",P$1,"")&IF(Q2="X",Q$1,"")
R3=IF(B3="X",B$1,"")&IF(C3="X",C$1,"")&IF(D3="X",D$1,"")&IF(E3="X",E$1,"")&IF(F3="X",F$1,"")&IF(G3="X",G$1,"")&IF(H3="X",H$1,"")&IF(I3="X",I$1,"")&IF(J3="X",J$1,"")&IF(K3="X",K$1,"")&IF(L3="X",L$1,"")&IF(M3="X",M$1,"")&IF(N3="X",N$1,"")&IF(O3="X",O$1,"")&IF(P3="X",P$1,"")&IF(Q3="X",Q$1,"")
R4=IF(B4="X",B$1,"")&IF(C4="X",C$1,"")&IF(D4="X",D$1,"")&IF(E4="X",E$1,"")&IF(F4="X",F$1,"")&IF(G4="X",G$1,"")&IF(H4="X",H$1,"")&IF(I4="X",I$1,"")&IF(J4="X",J$1,"")&IF(K4="X",K$1,"")&IF(L4="X",L$1,"")&IF(M4="X",M$1,"")&IF(N4="X",N$1,"")&IF(O4="X",O$1,"")&IF(P4="X",P$1,"")&IF(Q4="X",Q$1,"")
R5=IF(B5="X",B$1,"")&IF(C5="X",C$1,"")&IF(D5="X",D$1,"")&IF(E5="X",E$1,"")&IF(F5="X",F$1,"")&IF(G5="X",G$1,"")&IF(H5="X",H$1,"")&IF(I5="X",I$1,"")&IF(J5="X",J$1,"")&IF(K5="X",K$1,"")&IF(L5="X",L$1,"")&IF(M5="X",M$1,"")&IF(N5="X",N$1,"")&IF(O5="X",O$1,"")&IF(P5="X",P$1,"")&IF(Q5="X",Q$1,"")
R6=IF(B6="X",B$1,"")&IF(C6="X",C$1,"")&IF(D6="X",D$1,"")&IF(E6="X",E$1,"")&IF(F6="X",F$1,"")&IF(G6="X",G$1,"")&IF(H6="X",H$1,"")&IF(I6="X",I$1,"")&IF(J6="X",J$1,"")&IF(K6="X",K$1,"")&IF(L6="X",L$1,"")&IF(M6="X",M$1,"")&IF(N6="X",N$1,"")&IF(O6="X",O$1,"")&IF(P6="X",P$1,"")&IF(Q6="X",Q$1,"")
R7=IF(B7="X",B$1,"")&IF(C7="X",C$1,"")&IF(D7="X",D$1,"")&IF(E7="X",E$1,"")&IF(F7="X",F$1,"")&IF(G7="X",G$1,"")&IF(H7="X",H$1,"")&IF(I7="X",I$1,"")&IF(J7="X",J$1,"")&IF(K7="X",K$1,"")&IF(L7="X",L$1,"")&IF(M7="X",M$1,"")&IF(N7="X",N$1,"")&IF(O7="X",O$1,"")&IF(P7="X",P$1,"")&IF(Q7="X",Q$1,"")
R8=IF(B8="X",B$1,"")&IF(C8="X",C$1,"")&IF(D8="X",D$1,"")&IF(E8="X",E$1,"")&IF(F8="X",F$1,"")&IF(G8="X",G$1,"")&IF(H8="X",H$1,"")&IF(I8="X",I$1,"")&IF(J8="X",J$1,"")&IF(K8="X",K$1,"")&IF(L8="X",L$1,"")&IF(M8="X",M$1,"")&IF(N8="X",N$1,"")&IF(O8="X",O$1,"")&IF(P8="X",P$1,"")&IF(Q8="X",Q$1,"")
R9=IF(B9="X",B$1,"")&IF(C9="X",C$1,"")&IF(D9="X",D$1,"")&IF(E9="X",E$1,"")&IF(F9="X",F$1,"")&IF(G9="X",G$1,"")&IF(H9="X",H$1,"")&IF(I9="X",I$1,"")&IF(J9="X",J$1,"")&IF(K9="X",K$1,"")&IF(L9="X",L$1,"")&IF(M9="X",M$1,"")&IF(N9="X",N$1,"")&IF(O9="X",O$1,"")&IF(P9="X",P$1,"")&IF(Q9="X",Q$1,"")
 

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,673
Messages
5,838,706
Members
430,564
Latest member
Raeyven

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