1

I am struggling a bit with my IF statement and was wondering if I can get some help. I made a new column and want to have it filled based on three other cells. For example, you can see the attachment below. I want the new column to say, for example, F2 should just return Male, F3 should return Male 18+. F4 should return Male 18+ College Degree, and F5 should return 18+ College Degree.

Sample data

Sample data

Greenonline
  • 2,235
  • 11
  • 24
  • 30
Robert Wilson
  • 11
  • 1
  • 3
    Welcome to Super User. Some diagrams of your data would help us understand your question. Please [edit] your question to include some example (mock) data (before and after). See [Format Text as a Table](https://www.tablesgenerator.com/text_tables) for a web utility that will help you to create a nice data table you can paste into your question. – DavidPostill Jul 12 '22 at 14:48
  • 1
    Use multiple `IF` Statements each separated by `&" "&` (concatenation of a blank space between each IF). – gns100 Jul 12 '22 at 16:14

3 Answers3

2

If you really want to stick to a regular IF function, you can simply combine the various conditions. In particular, instead of value_if_false, insert the next IF statement. Both approaches assume that your data is in the range A1:C5, and you paste the formula into cell D2 and copy it down. The values are divided by ";", but you can simply delete this if you do not need it.

IF Function:

=IF(AND(A2:C2)=TRUE,"Male; 18+; College Degree",
IF(AND(A2,B2)=TRUE,"Male; 18+",
IF(AND(A2,C2)=TRUE,"Male; College Degree",
IF(AND(B2,C2)=TRUE,"18+; College Degree",
IF(A2=TRUE,"Male",IF(B2=TRUE,"18+",IF(C2=TRUE,"College Degree","ERROR")))))))

Since this procedure is quite tedious, especially if you have multiple conditions, another approach would be to simply use the FILTER function for the cases that are TRUE and then join them accordingly.

=TEXTJOIN("; ",TRUE,FILTER($A$1:$C$1,A2:C2=TRUE))
karl
  • 850
  • 3
  • 10
1

Try this formula:

=CONCAT(FILTER($A$1:$C$1,A2:C2=TRUE)&" ","")

enter image description here

Lee
  • 2,923
  • 1
  • 6
  • 8
0

In case you don't have a recent enough version of Excel to use dynamic arrays, just a concatenated series of IFs would do the job, but not great to scale to many columns.

You will need to add spaces, and then use TRIM to get rid of any "unused" ones at the ends or a double in the middle (for "Male__College degree"):

=TRIM(IF($A2,$A$1,"") &" "& IF($B2,$B$1,"") &" "& IF($C2,$C$1,""))

This relies on the fact that $A2 = TRUE is the same as just $A2. Since $A2 is already true or false, you don't need to test if TRUE=TRUE (like 1=1).

If you don't actually have True and False already, but something else such as Yes/No, you need to extend each IF as a more formal condition:

=...IF($A2="Yes",$A$1,"")...
AdamV
  • 6,177
  • 2
  • 22
  • 38