Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Why doesn't this formula work on Airtable

Topic Labels: Formulas
Solved
Jump to Solution
1452 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Vijay_chen
6 - Interface Innovator
6 - Interface Innovator

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
Justin_Barrett
18 - Pluto
18 - Pluto

@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.

Justin_Barrett
18 - Pluto
18 - Pluto

@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: