IF Formula Based on Several Columns/Fields

[Selina_Garcia]

Hi! I have little to no knowledge on spreadsheet formulas and codes, so the formula function of Airtable baffles me. Thus, I am completely floored on trying to figure out how to create an IF Formula on a 4th column based on the data of 3 other Columns/Fields.

I’m creating a database for art collections, and I am trying to keep record of what artwork goes where. Thus, I have 3 columns, “Buyer,” “Shelf Location” (in the gallery), and “Exhibition” (where the artwork is loaned for an exhibit). I am making a 4th column that makes for a quicker view for other people looking at the database and I want it to reflect the following:

IF({Buyer}!=0, “Sold,”
IF({Shelf Location}!=0, “Inventory,”
IF({Exhibition}!=0, “On Loan”))

But when I input this formula or some variations of it (I tried adding AND/OR), I always get an error.

By the way, “Buyer” and “Exhibition” columns are linked to other columns for further details on the matter.

I really appreciate anyone who can help! Thank you!

[JonathanBowen · Accepted solution]

Hi @Selina_Garcia - at a simple level, this formula works:

IF(Buyer, 'Sold', IF({Shelf Location}, 'Inventory', IF(Exhibition, 'On loan', '')))

However, this doesn’t work if two or more of the 3 fields are populated:

On the assumption that a piece of art must have one and only one of the 3 possible statuses, you could use something like this formula to also note when two or more columns are populated:

IF(
  AND(Buyer, NOT({Shelf Location}), NOT(Exhibition)),
  'Sold', 
  IF(
    AND({Shelf Location}, NOT(Buyer), NOT(Exhibition)),
    'Inventory', 
    IF(
      AND(Exhibition, NOT({Shelf Location}), NOT(Buyer)),
      'On loan', 
      '❌❌❌❌'
    )
  )
)

This is just a bit of error checking/data validation which might make the base data a bit better.

As an aside you don’t have to specify what a field is equal to in an IF statement (unless its actual value matters) - you can just say:

IF(Buyer, ......)

Which translates to “if Buyer exists”, i.e. has some value, rather than empty.

Hope this helps

JB

[Selina_Garcia]

Oh, wow! This works so well. Thank you so much, JB!!! :grinning_face_with_smiling_eyes: