Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Check Value Against Column of Stored Values in Other Table (XLOOKUP...ish?)

Topic Labels: Automations Data Formulas
887 1
cancel
Showing results for 
Search instead for 
Did you mean: 
olosnah1977
4 - Data Explorer
4 - Data Explorer

Hi all, first-time poster. I'm working with form submissions right now, and I'd like to check if the free text of a Location form field matches any value in an existing table's list of locations. 

For instance, if we received 'Bayview South' in the 'Location' form field, then I would want to check whether 'Bayview South' appears on the below list: 

 

AB
LOCATIONSUM
Bayview North1
Bayview South1
Sea Breeze1
Palms North1

 In Excel, I would normally just use an XLOOKUP and IF formula on the above table with the below syntax: 

IF(XLOOKUP("Bayview South",A:A,B:B,"0")>1,"In list","Not in list")

In this case, the XLOOKUP formula would check all of column A for 'Bayview South,' returning a 1 if 'Bayview South' exists and 0 if it doesn't. The IF formula then returns an 'In list' value if 'Bayview South' is anywhere in the list or a 'Not in list' value if 'Bayview South' is not in the list. 

The reason I'm looking for this solution is to run exception automations if a value does appear in the list. I tried using an automation to drop the Location value from the form field into a Location lookup field (linked to the Location exception list). However, the issue is that Airtable creates a new value in the linked table if the value doesn't already exist, so there's no way to differentiate between a value that exists in the list already and one that doesn't. 

Anyway, apologies for the wall of text, thank you in advance for any help with this!

1 Reply 1

I assume that list of locations is its own table?  If so, try using a Find Records action to look in that table, and then use a conditional to either put "In list" or "Not in list" in your triggering record