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

Topic Labels: Automations Data Formulas
197 1
Showing results for 
Search instead for 
Did you mean: 
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: 


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