Skip to main content
Solved

Formula to lookup whether a lookup field contains a value

A

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")

 

View original
Did this topic help you find an answer to your question?

2 replies

Databaser
Forum|alt.badge.img+4
  • Databaser
  • Inspiring
  • 866 replies
  • 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
ScottWorld
A
B
3 people like this
  • ScottWorld
    ScottWorld
  • A
    Alastair_Budge1
  • B
    beamzen
A
  • Alastair_Budge1
  • Author
  • Participating Frequently
  • 8 replies
  • October 26, 2023
Databaser wrote:

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 

Databaser
1 person likes this
  • Databaser
    Databaser

Reply


Most helpful members this week

Terms & Conditions