In Table 1, can I search a column for text that exists in Table 2 and return a text in another column in Table 2 back to a new column in Table 1?

En masse, I need to look for “Strawberry” or “Lemon” or whatever value is in “Flavor” column of my “Flavor” table in the Description column of my “Products” table, and return the “Flavor Type” from my “Flavor Table” in the “Flavor Type” column of the “Products” table.

How can this be accomplished?

Hi Kolbi! Yes you can do this using lookups and our formula language. I have attached an example doc of this here based on what I think you were describing. I am a Customer Success Manager at Coda and if you want to schedule a call to walk through this let me know! :slightly_smiling_face:

1 Like

I’m interested in a bit more depth on this one, as I need to parse inconsistent strings through this filter to determine the flavor name so that I can lookup the correlated flavor category.

It’s basically along these lines: Search the string in [Flavor Catalog].[Description] for any values that exist in [Flavor].[Flavor Name] and for whichever [Flavor] table row contains a record in that is found in any of the words that exist in [Flavor Catalog].[Description], return the identical [Flavor Name]. Using this result, then return the [Flavor Type].

The closest thing I have been able to come to is this:

LOOKUP([Flavor Test Table],[Flavor Test Table].[Flavor Name Test],ForEach(thisRow.Description.SUBSTITUTE(“,”,“”).SUBSTITUTE(“,”,“”).SPLIT(“;”).split(" “),lookup([Flavor Test Table],[Flavor Test Table].[Flavor Name Test] ,CurrentValue.UPPER())).unique().ListCombine().ListCombine().TOTEXT().SUBSTITUTE(”,“,”“).SUBSTITUTE(”,“,”"))

I think it may be working, but I would love to know if there is an easier or simpler (or more consistent) formula to do this

I think this table that I’ve added to your doc, @Tristin_Kott, describes well what I am after.