Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Why doesn't this formula work on Airtable

Topic Labels: Formulas
Solved
Jump to Solution
439 3
cancel
Showing results for 
Search instead for 
Did you mean: 

IF(FIND(“EN 60065: 2014”,LVD),“YES”,"")
This can work in Excel, but does not woFIND FIND2 rk in Airtable, why?

1 Solution

Accepted Solutions

@Bill.French I think the mixed quotes are just a forum hiccup. The screenshot shows the proper straight quotes in the formula.

@Vijay_chen This is because your formula is pulling from a lookup field. Lookup fields most often return arrays, not strings, and the FIND() function requires a string. If the related link field is only pulling from a single record, it will sometimes auto-convert to a string, which is why some of your formulas are creating the desired output, but it’s still best to force it to a string in your formula when you need it to be treated like a string. To force the lookup’s output into a string, concatenate it with an empty string in your formula. You can also drop the ending empty string from your formula, as Airtable will default to a blank value for the result if the “True” portion of the function isn’t executed.

With those changes, your formula becomes this:

IF(FIND("EN 60065: 2014", LVD & ""),"YES")

See Solution in Thread

3 Replies 3

My guess is it’s the different quotation mark types you are using - it’s a mix between " and “ - I would start by removing all double-quotes and type in only single quotes to see if that works.

@Bill.French I think the mixed quotes are just a forum hiccup. The screenshot shows the proper straight quotes in the formula.

@Vijay_chen This is because your formula is pulling from a lookup field. Lookup fields most often return arrays, not strings, and the FIND() function requires a string. If the related link field is only pulling from a single record, it will sometimes auto-convert to a string, which is why some of your formulas are creating the desired output, but it’s still best to force it to a string in your formula when you need it to be treated like a string. To force the lookup’s output into a string, concatenate it with an empty string in your formula. You can also drop the ending empty string from your formula, as Airtable will default to a blank value for the result if the “True” portion of the function isn’t executed.

With those changes, your formula becomes this:

IF(FIND("EN 60065: 2014", LVD & ""),"YES")

amazing :laughing: ,thank you very much :smiling_face_with_three_hearts: