Formula for Table Lookup—Two fields, an ID string and a string in a Rollup

[VitaliyG]

Thanks for taking a look in this thread.

I am looking to create a zapier step that checks for a row in a table which contains 1) a certain ID (exists as string), and 2) to see if a certain string exists as part of a rollup.

How would I write the formula for this?

This is what I have so far… it’s good for finding rows that have only one item in the rollup, but if I need to find a certain item within a rollup, it says none exist.

AND({ID}=‘abcdefg123’, {Rollup}=‘xyz999999’)

Thanks again! Happy holidays!

[Justin_Barrett · Accepted solution]

The formula format you listed will only look for an exact match for both the {ID} and {Rollup} fields. To look for string inside another string (i.e. inside the rollup field), you’ll need to use either the FIND() or SEARCH() functions. Something like this:

AND(ID="abc123", FIND("xyz999", Rollup))

[VitaliyG]

Hi Justin, thanks for your reply. I figured it was looking for an exact match, but wasn’t sure how to search for a string within a rollup.

Here’s the exact formula I’m using but I’m having trouble getting it to find anything in airtable…

AND({Syndicator MS ID}="5fc16410bd68cf0004b4f2ad", FIND("5fc1925e8685fb63f227fb8f", {Deals Rollup}))

The “Syndicator MS ID” is exactly how it exists in the field, and the FIND input is one of the rolled up strings that’s included in the “Deals Rollup” field.

What am I doing wrong here?

Thanks for your help!

UPDATE: Ok I got it to work. Each of my items in a Rollup were surrounded by double quotes. So in order to FIND the string, I had to wrap the FIND input within the formula in quotes. So here is the final formula:

AND({Syndicator MS ID}='5fc16410bd68cf0004b4f2ad', FIND('"5fc1925e8685fb63f227fb8f"', {Deals Rollup}))

[VitaliyG]

@Justin_Barrett While the formula works in Airtable, I’m having trouble getting it to work in Zapier. Here’s the exact formula I’m using in Zapier. Below I’ve also included the screenshot of what the record looks like in Airtable so you can see for reference.

AND({Syndicator MS ID}='<Trigger Value>5fc16410bd68cf0004b4f2ad<Trigger Value>', FIND('<Trigger Value>"5fc1925e8685fb63f227fb8f"<Trigger Value>', {Deals Rollup}))

How the Search Formula appears in Zapier:

Record in Airtable:

What would be some reasons why it would work in Airtable but not in Zapier?

Thanks so much for your help.

[Justin_Barrett]

My gut says that the <Trigger Value> text that’s wrapped around the value is a likely cause of the problem. Do you know where those are coming from? The formula with values inserted should look like this:

AND({Syndicator MS ID}='5fc16410bd68cf0004b4f2ad', FIND('"5fc1925e8685fb63f227fb8f"', {Deals Rollup}))

[VitaliyG]

You’re totally right. I was under the impression I had to wrap inputs in to get them to work in Zapier. Check out this link: https://support.airtable.com/hc/en-us/articles/218151657-Using-Zapier-s-Multi-Step-Zaps-to-find-and-...

[Justin_Barrett]

I’ve only used Zapier a handful of times, but this is the first time I’ve seen that article and that recommendation that <TriggerValue> needs to be wrapped around the inputs. That’s just bizarre. It’s possible that the article is referring to an older version of the Airtable steps.

@ScottWorld have you ever seen this?

[ScottWorld]

Yes, unfortunately, we needed to wrap every input in <TriggerValue>. At least, it was still this way in September 2020 when I most recently setup Zapier for a client of mine. This is probably Reason #352 that I recommend Integromat over Zapier. :stuck_out_tongue_winking_eye:

But is @VitaliyG saying that this no longer is required anymore in Zapier?

It also looks like @VitaliyG’s original search formula might possibly have an extraneous pair of double quotation marks around the 2nd input.

[Justin_Barrett]

ScottWorld:

It also looks like @VitaliyG’s original search formula might possibly have an extraneous pair of double quotation marks around the 2nd input.

Those quotes are actually in the data, which is why they’re in the formula. However, if the rollup field is outputting a string and not an array, it shouldn’t matter if the quotes are there or not. The characters inside the quotes can still be found without them.

[VitaliyG]

Weird! Yeah, @Justin_Barrett’s solution worked when I removed <Trigger Value>.