# Combining results of IF()

Topic Labels: Formulas
1133 7
cancel
Showing results for
Did you mean:
4 - Data Explorer

Hi All,

Having trouble figuring out how to use IF, AND, concatenate for this situation:

If {Field 1} then return “Mary”, and
If {Field 2} then add “Bob” to the end of “Mary”, and then
If {Field 3} then add “Jake” to the end of “Mary” and “Bob”

It continues for all combinations of Field 1,2, or 3. This was one attempt:

IF(
{Field 1}, ‘Mary’
IF(
AND(
{Field1},
{Field 2}
),
‘Mary and Bob’

)
)

7 Replies 7
5 - Automation Enthusiast

Hi there! Welcome to the Airtable Community!

If I understand correctly, I think it would just be separating them out, and returning blanks if the field is empty.

Try this:

``````IF({Field 1}, "Mary","")
&
IF({Field 2}, "Bob","")
&
IF({Field 3}, "Jake","")
``````

If you are wanting to do combinations, (aka only showing Jake and Bob if BOTH Field 2 and Field 3 are present), you would want to do something like this.

``````IF({Field 1}, "Mary","")
&
IF(AND{Field 2},{Field 3}), "Bob & Jake","")
``````
4 - Data Explorer

I have spent a full day on this and I can not get anything to work! I have numerous date fields, {Date 1 } {Date 2 } {Date 3 } {Date 4 }. I want them to populate into another field as such:
Date 1: {Date 1}
Date 2: {Date 2 }
Date 3: {Date 3 }
Date 4: {Date 4 }
My problem is that sometimes not all these date fields are used and so I get an #error# in return for those records. How can I list the dates BUT skip the dates that are empty?
I have tried :
IF({Binding Date Fx},“Binding Date:”&{Binding Date Fx},“BLANK”)&“\n”&IF({Earnest \$ Fx}, “Earnest Money Due:”&{Earnest \$ Fx},“BLANK”)&“\n”&IF({DD Ends Fx}, “Due Diligence (Inspection Period) Ends:”&{DD Ends Fx},“BLANK”)&“\n”&IF({Financing Fx}, “Financing Cont. Ends:”&{Financing Fx},“BLANK”)&“\n”&IF({Appraisal Ends Fx}, “Appraisal Cont. Ends:”&{Appraisal Ends Fx},“BLANK”)&“\n”&IF({Closing Date Fx}, “Closing Date:”&{Closing Date Fx},“BLANK”)&“\n”&IF({Possession Date Fx}, “Possession Date:”&{Possession Date Fx},“BLANK”) BUT I still get #ERROR#. Any help would be greatly appreciated.

11 - Venus

Hey @HS_789!

This post is kinda old, but noticed it and wanted to give it a shot:

``````IF(
{Field 1},
"Mary"
)
&
IF(
AND(
{Field 1},
{Field 2},
{Field 3}
),
", Bob",
IF(
AND(
{Field 1},
{Field 2}
),
" and Bob",
IF(
{Field 2},
"Bob"
)
)
)
&
IF(
AND(
{Field 1},
{Field 2},
{Field 3}
),
", and Jake",
IF(
AND(
OR(
AND(
{Field 1},
{Field 3}
),
AND(
{Field 2},
{Field 3}
)
)
),
" and Jake",
IF(
{Field 3},
"Jake"
)
)
)
``````
11 - Venus

Hey @Jennifer_Davidson!
I think this might actually justify its own thread, but I’ll reply here anyways since I wanted to make sure you got an answer on this.

The first approach to your formula is this:

``````IF(
{Binding Date Fx},
"Binding Date: " & DATETIME_FORMAT({Binding Date Fx}, 'L')
)
& '\n' &
IF(
{Earnest \$ Fx},
"Earnest Money Due: " & DATETIME_FORMAT({Earnest \$ Fx}, 'L')
)
& '\n' &
IF(
{DD Ends Fx},
"Due Diligence (Inspection Period) Ends: " & DATETIME_FORMAT({DD Ends Fx}, 'L')
)
& "\n" &
IF(
{Financing Fx},
"Financing cont. Ends: " & DATETIME_FORMAT({Financing Fx}, 'L')
)
& '\n' &
IF(
{Appraisal Ends Fx},
"Appraisal Cont. Ends: " & DATETIME_FORMAT({Appraisal Ends Fx}, 'L')
)
& "\n" &
IF(
{Closing Date Fx},
"Closing Date: " & DATETIME_FORMAT({Closing Date Fx}, 'L')
)
& "\n" &
IF(
{Possession Date Fx},
"Possession Date: " & DATETIME_FORMAT({Possession Date Fx}, 'L')
)
``````

This formula will return this behavior:

Now, this solution works fine, but there is something that bugs me about it.

Every IF function is separated in the formula by a `& "\n" &` statement.

But since there’s a possibility that a date field is blank, then it will just leave a blank line between possible values. This leads to this type of behavior:

Again, this might not bother you, but if you want to keep it tight, here’s a formula version that will eliminate this behavior.

``````REGEX_REPLACE(
IF(
{Binding Date Fx},
"Binding Date: " & DATETIME_FORMAT({Binding Date Fx}, 'L') & 'line'
)
&
IF(
{Earnest \$ Fx},
"Earnest Money Due: " & DATETIME_FORMAT({Earnest \$ Fx}, 'L') & 'line'
)
&
IF(
{DD Ends Fx},
"Due Diligence (Inspection Period) Ends: " & DATETIME_FORMAT({DD Ends Fx}, 'L') & 'line'
)
&
IF(
{Financing Fx},
"Financing cont. Ends: " & DATETIME_FORMAT({Financing Fx}, 'L') & 'line'
)
&
IF(
{Appraisal Ends Fx},
"Appraisal Cont. Ends: " & DATETIME_FORMAT({Appraisal Ends Fx}, 'L') & 'line'
)
&
IF(
{Closing Date Fx},
"Closing Date: " & DATETIME_FORMAT({Closing Date Fx}, 'L') & 'line'
)
&
IF(
{Possession Date Fx},
"Possession Date: " & DATETIME_FORMAT({Possession Date Fx}, 'L') & 'line'
),
'line',
'\n'
)
``````

As you add and remove dates, it will adjust the formatting appropriately.

18 - Pluto

Here is a slight variation on Ben’s formula. This style is slightly more compact. I use this pattern a lot. The use of `CONCATENATE()` removes the need for the `&` between `IF` functions. The `'\n'` new line characters are inserted directly instead of being replaced with a regex. The addition of `TRIM()` remove any trailing newline character.

``````TRIM(CONCATENATE(
IF(
{Binding Date Fx},
"Binding Date: " & DATETIME_FORMAT({Binding Date Fx}, 'L') & '\n'
),
IF(
{Earnest \$ Fx},
"Earnest Money Due: " & DATETIME_FORMAT({Earnest \$ Fx}, 'L') & '\n'
),
IF(
{DD Ends Fx},
"Due Diligence (Inspection Period) Ends: " & DATETIME_FORMAT({DD Ends Fx}, 'L') & '\n'
),
IF(
{Financing Fx},
"Financing cont. Ends: " & DATETIME_FORMAT({Financing Fx}, 'L') & '\n'
),
IF(
{Appraisal Ends Fx},
"Appraisal Cont. Ends: " & DATETIME_FORMAT({Appraisal Ends Fx}, 'L') & '\n'
),
IF(
{Closing Date Fx},
"Closing Date: " & DATETIME_FORMAT({Closing Date Fx}, 'L') & '\n'
),
IF(
{Possession Date Fx},
"Possession Date: " & DATETIME_FORMAT({Possession Date Fx}, 'L') & '\n'
)
))
``````
11 - Venus

Oooo!

It doesn’t. `CONCATENATE()` does the same as `&`, but I find it a little cleaner in multi-line formulas.
I added the newline characters directly to each line using `& '\n'`, instead of `& 'line'` combined with the REGEX.