Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Rollup + IF(SEARCH())

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

0
6102
12

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 19, 2020 04:12 PM

Hello,

Here’s my base:

I’m using a Roll Up to see if the URL in “Leads” table includes the UTM in “Inputs” table. If it does, I want it to add the corresponding “Source” from the “Inputs” table as the “Referral Source” in the “Leads table”

I thought I might be able to achieve this as adding this formula with the Roll Up:

IF(SEARCH({UTM},{Input}){Source})

But it tells me that’s an invalid formula.

Thanks for any help!

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 20, 2020 10:48 AM

Airtable cannot replicate your Excel formula, but your idea of directly using a formula without referencing the other table will work.

Airtable formulas are very picky about syntax. They require straight quotes instead of curly quotes. Also, a single misplaced, missing, or extra character (parenthesis, comma, quote) can throw off the entire formula.

I suggest that you try a simpler version that uses concatenation instead of nesting, and make sure you do not have any curly quotes. `“ ”`

versus `"`

.

```
IF(SEARCH("?utm_source=youtube&utm_medium=video",{Input}),"YouTube")
&
IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_term=implantstampa",{Input}),"Google AdWords Implants Tampa")
&
IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_term=implantsorlando",{Input}),"Google AdWords Implants Orlando")
```

You could also split up the searches to search for source, medium, and term in different parts of the function:

```
IF(SEARCH("utm_source=youtube",{Input}),"YouTube")
&
IF(SEARCH("utm_source=google",{Input}),"Google")
& " " &
IF(SEARCH("utm_medium=cpc",{Input}),"AdWords")
& " " &
IF(SEARCH("utm_term=implantstampa",{Input}),"Implants Tampa")
```

Or you could use more complex formulas for spliting up the string.

Reply

12 Replies 12

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 19, 2020 06:06 PM

A rollup needs to have an aggregation function for its values. You can nest the aggregation function inside another function, but you cannot completely omit the aggregation function in a rollup.

Take a look a conditional rollups to see if that will work for you.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 19, 2020 07:57 PM

Thank you for your reply. I’m apologize, I’m not very adept in this field, but doesn’t the SEARCH() act as an aggregate?

Where as “=” looks for an exact match, SEARCH() looks to see if a specified text contains another specified text within it. But maybe my definition of aggregate is wrong.

I may need someone to point me to the right formula for this one. I’m having trouble finding the right solution from the conditional rollups documentation.

I just need a way to check if Web URLs contain a UTM string from a list of 23 UTM strings, and if they do I need them to interpret that string as a different text.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 19, 2020 08:37 PM

Also, perhaps I’m reference my fields in the linked table incorrectly.

In my linked table, I have a field called “UTM” and field called “Source.” But when I write {UTM} and {Source} in my formula, it says those fields don’t exist. I’ve made sure my URL field is linked to that table.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 19, 2020 08:38 PM

An aggregation formula (in the context of Airtable formulas) takes a list of values and converts it to a single value, such as a sum, minimum value, a single string of text, etc. The `SEARCH`

function looks for a value in a single string.

You could perform a `SEARCH`

after aggregating the values in the rollup field.

```
IF(
SEARCH({UTM}, ARRAYJOIN(values)),
"UTM found in rollup values",
"UTM NOT found in rollup values",
)
```

Can you post a screen shot? It would make it easier to understand the exact field types, field names, and values.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 19, 2020 08:42 PM

The empty “Source” field in “Leads” is where I want to display the correct source from “Inputs”

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 19, 2020 10:12 PM

Thank you for posting the screen shot.

I don’t see any linked records or any rollup fields in your screen shot, but it looks like you used to have a lookup field. So maybe you changed the design of the fields? Rollups only work if you already have linked records.

It looks like you need to link the tables in a many-to-many relationship, so that each record in the [Leads] table is linked to the the corresponding records in the [inputs] table. After all the records are linked, a rollup can then show a list of {Source} values for each record in the [inputs] table.

A rollup or formula cannot create the many-to-many relationships for you. If you know JavaScript (or can hire someone who does), you might want to look into a Scripting Block solution.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 20, 2020 04:01 AM

Yes, my “Input” field in **Leads** is linked to the “UTM” field in **Inputs** in a many-to-many relationship. Its an old screenshot so maybe I hadn’t done that before I took the screenshot.

Maybe I should skip the Roll Up and just do a formula only?

In Excel, this all can be achieved by a formula. I’ve tested the formula and it works. Maybe you can help me translate it to an Airtable formula?

=IFERROR(LOOKUP(2, 1/(ISNUMBER(SEARCH(Inputs!$A$1:$A$46,B2))), Inputs!$B$1:$B$46),"")

In this formula, “Inputs!$A$1:$A$46” contain all the different possible UTM strings

Column B (in the Leads sheet, where the formula is written in column C) contains the URLs we are checking to see if they include one of our UTMs. For each row, this changes to B2, B3, B4, etc.

If it finds one, it displays the corresponding Source found in Inputs!$B$1:$B$46

If it doesn’t find one, it leaves the cell blank.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 20, 2020 07:02 AM

Alternatively, but not ideally, I could skip referencing another table and just write everything into a formula like this:

IF(SEARCH("?utm_source=youtube&utm_medium=video”,{Input}),”YouTube”,

IF(SEARCH(“?utm_source=google&utm_medium=cpc&utm_term=tampabrand",{Input}),“Google AdWords Tampa Brand”,

IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_term=implantstampa",{Input}),“Google AdWords Implants Tampa”,

IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_term=implantsorlando",{Input}),“Google AdWords Implants Orlando”,

IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_term=orlandobrand",{Input}),“Google AdWords Orlando Brand”,

IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_term=srtampa",{Input}),“Search Remarketing Tampa”,

IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_campaign=4_tampa",{Input}),“GA AllOn4 Tampa”,

IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_campaign=4_orlando",{Input}),“GA AllOn4 Orlando”,

IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_term=conqtampa",{Input}),“Google AdWords Conquest Tampa”,

IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_term=conqorlando",{Input}),“Google AdWords Conquest Orlando”,

IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_campaign=brand-us",{Input}),“Brand US”,

IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_campaign=jacksonville",{Input}),“D5 Conquest”,

IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_term=zygomatic",{Input}),“Zygomatic Dental Implants”,

IF(SEARCH(”?utm_source=google&utm_medium=cpc&utm_campaign=periodontal",{Input}),“Periodontal”,

IF(SEARCH(”?utm_source=bing&utm_medium=cpc&utm_campaign=ntl&utm_term=allon4",{Input}),“Bing AllOn4 US”,

IF(SEARCH(”?utm_source=bing&utm_medium=cpc&utm_term=orlandobrand",{Input}),“Bing Brand Orlando”,

IF(SEARCH(”?utm_source=bing&utm_medium=cpc&utm_term=tampabrand",{Input}),“Bing Brand Tampa”,

IF(SEARCH(”?utm_source=bing&utm_medium=cpc&utm_campaign=ntl",{Input}),“Bing National”,

IF(SEARCH(”?utm_source=bing&utm_medium=cpc&utm_term=conqorlando",{Input}),“Bing Conquest Orlando”,

IF(SEARCH(”?utm_source=bing&utm_medium=cpc&utm_term=conqtampa",{Input}),“Bing Conquest Tampa”,

IF(SEARCH(”?utm_source=bing&utm_medium=cpc&utm_term=implantsorlando",{Input}),“Bing Dental Implants Orlando”,

IF(SEARCH(”?utm_source=bing&utm_medium=cpc&utm_term=implantstampa",{Input}),“Bing Dental Implants Tampa”,

IF(SEARCH(”?utm_source=bing&utm_medium=cpc&utm_term=zygomatic",{Input}),"Bing Zygomatic“

)))))))))))))))))))))))

But this tells me it’s an invalid formula. Anyone see a problem with how I’m structuring it? I wrote it using the solution found in this thread: Nested IF(SEARCH()) Formulas

I also tried it without the brackets around Input, as in {Input}. But it still said it was invalid.

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 20, 2020 10:48 AM

Airtable cannot replicate your Excel formula, but your idea of directly using a formula without referencing the other table will work.

Airtable formulas are very picky about syntax. They require straight quotes instead of curly quotes. Also, a single misplaced, missing, or extra character (parenthesis, comma, quote) can throw off the entire formula.

I suggest that you try a simpler version that uses concatenation instead of nesting, and make sure you do not have any curly quotes. `“ ”`

versus `"`

.

```
IF(SEARCH("?utm_source=youtube&utm_medium=video",{Input}),"YouTube")
&
IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_term=implantstampa",{Input}),"Google AdWords Implants Tampa")
&
IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_term=implantsorlando",{Input}),"Google AdWords Implants Orlando")
```

You could also split up the searches to search for source, medium, and term in different parts of the function:

```
IF(SEARCH("utm_source=youtube",{Input}),"YouTube")
&
IF(SEARCH("utm_source=google",{Input}),"Google")
& " " &
IF(SEARCH("utm_medium=cpc",{Input}),"AdWords")
& " " &
IF(SEARCH("utm_term=implantstampa",{Input}),"Implants Tampa")
```

Or you could use more complex formulas for spliting up the string.

Reply