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:
| A | B |
| LOCATION | SUM |
| Bayview North | 1 |
| Bayview South | 1 |
| Sea Breeze | 1 |
| Palms North | 1 |
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!
