If, find difficulties

Parkigrocer_you · ‎Nov 27, 2019

I’ve read numerous examples, but can’t seem to get what I need working.

At a guess, I’d say this is a very simple formula.

Essentially, I have a table with a field ‘Box size’ where people can select small, medium or large sizes. There are also ‘small box with eggs’, ‘small box with coffee’ as just a couple of variations that can be selected.

I have tried to create a formula field that simply searches for ‘Small’, ‘Medium’ or ‘Large’ from the records ‘Box size’ field and places the simplified value into a new field called ‘Simple box size’ so it can be used to display on reports etc.

Is someone please able to show me an example so I am able to get my head around this?

Thanks so much.

Cheers,

Dave.

JonathanBowen · ‎Nov 28, 2019

Hi @Parkigrocer_your_com - try this:

IF(
  FIND('small', LOWER({Box Size})), 
  'Small',
  IF(
    FIND('medium', LOWER({Box Size})), 
    'Medium',
    IF(
      FIND('large', LOWER({Box Size})), 
      'Large',
      ''
    )    
  )  
)

The FIND() function is case sensitive, so I’ve lower-cased the Box Size value first so that you can get a match on both ‘small’ and ‘Small’. The formula also handles options that are not small, medium or large and an empty field.

JB

Parkigrocer_you · ‎Nov 28, 2019

Wow, thank you so much JB - that works perfectly! Can I presume the last empty quotes are the exception value?

Thanks again.

JonathanBowen · ‎Nov 28, 2019

You’re welcome.

Yes, this returns an empty string if none of the prior conditions are matched (i.e. none of small, medium or large are found).

Parkigrocer_you · ‎Nov 28, 2019

Ahh, great. You’re a star!

Justin_Barrett · ‎Nov 28, 2019

FWIW, those final empty quotes are optional. If the third part of an IF() function is omitted, Airtable automatically returns the proper BLANK() equivalent based on the other data returned.