Using IF and FIND formula yielding strange results

[Joseph_Boerner]

Hi… First post here…

I’m trying to simply search a lookup field for a certain Order ID number (397147) and have the formula field display “VIP” if it is found, and “NON-VIP” if it is not found.

The formula is only showing “VIP” in those records where the Order ID number that is being searched for is either the first or last Order ID in the lookup field. If 397147 is in the lookup field, but not the first or last Order ID, it displays “Non-VIP”.

Here’s the formula:
IF(FIND(“397147”,{Product1 ID (from Orders)}), “VIP”,“Non-VIP”)

Thanks in advance for any help!

[JonathanBowen]

Hi @Joseph_Boerner - try converting your lookup field to a string - the find should then work with this. A lookup field is of type “array” and the FIND method works on a string. This works for me:

with formula:

IF(FIND(987654, {Product ID Lookup} & ''), 'Found', 'Not Found')

The conversion to string is this:

{Product ID Lookup} & ''