Skip to main content
Solved

Formula to lookup whether a lookup field contains a value

  • October 23, 2023
  • 2 replies
  • 260 views
A
Forum|alt.badge.img+6

I'm trying to write a formula to check whether a lookup field contains a specific value.

These lookup fields might contain one or multiple values, e.g.

  1. apples
  2. apples, pears
  3. pears, bananas, strawberries

I've been using the formula 

IF(FIND("apples", {fruitName}), "yes", "no")

 
In this case, I would expect 1 and 2 to return as "yes", but only 1 is returning yes. Find seems to require an exact match, and if there are other values in the lookup field, it returns false. 
Does anyone know what I'm doing wrong here?

Best answer by Databaser

The FIND() function works with strings, not arrays. So add &"" to convert the data of your lookup field into a string. 

IF(FIND("apples", {fruitName}&""), "yes", "no")

 

This topic has been closed for replies.

2 replies

Databaser
Forum|alt.badge.img+25
  • Databaser
  • Brainy
  • Answer
  • October 23, 2023

The FIND() function works with strings, not arrays. So add &"" to convert the data of your lookup field into a string. 

IF(FIND("apples", {fruitName}&""), "yes", "no")

 

Jan - Databaser
    A
    Forum|alt.badge.img+6
    • Alastair_Budge1
    • Author
    • Participating Frequently
    • October 26, 2023

    The FIND() function works with strings, not arrays. So add &"" to convert the data of your lookup field into a string. 

    IF(FIND("apples", {fruitName}&""), "yes", "no")

     


    Amazing, this worked perfectly. Thank you very much @Databaser 

      Terms & ConditionsAccessibility statement