IF(FIND) Formula Help

I am looking for help with an IF(FIND) formula (I think that’s what I need it to be). Here’s the result I’m looking for.

If {Order Line Items} contains “8009586” display 2.5 FT Core Display, If {Order Line Items} contains “8009587” display 5 FT Core Display, if neither display None.

Here’s what I have written but it’s not working: IF(FIND(“8009586”, {Order Line Items}), ‘2.5 FT Core Display’, IF(FIND(“8009567”, {Order Line Items}), ‘5 FT Core Display’), ‘None’))

I’ve created a work-around by creating this formula: IF(FIND(“8009586”, {Order Line Items}), ‘2.5 FT Core Display’, ‘5 FT Core Display’). This will show the first part and defaults to the 5 FT Core Display if the first part number is not there. It works but could have holes if neither parts are actually listed.

Any help on refining this would be appreciated!

Try adding &"" to the end of each reference to {Order Line Items}:

IF(
   FIND(
      "8009586",
      {Order Line Items}&""
   ),
   "2.5 FT Core Display",
   IF(
      FIND(
         "8009567",
         {Order Line Items}&""
      ),
      "5 FT Core Display",
      "None"
   )
)
1 Like

That did it! Thanks!!

1 Like

I think the issue was just that your {Order Line Items} field contains linked records (I’m assuming), and so it’s outputting an array, rather than a string. The FIND() function doesn’t know how to find a string in an array, so it was failing to return anything. By adding &"" to the end of your references, you are coercing the array that is output by {Order Line Items} into behaving as a string instead of an array, because you are concatenating an empty string onto the end of it.

1 Like

Thanks for the explanation Jeremy, the field does contain linked records in order to create a multi-line, multi-product packing list.