IF Function & formula's using lookup

[Max-Antoine_EDI]

Hi Everyone,

I have trouble digging into IF function, let me explain my problem :slightly_smiling_face:

We have different type of client. Each a qualified by (let’s say) “A”, “B” or “C” in a single select field. The type of client define the hourly rate (in €). I use a “IF” statement to automatically update the hourly rate depending on the type of client.

No problem for now.

The table is linked to another one where I attribute each client on different group (these are trainings). Trainings have X hours, and I use a formula field to simply “Hourly rate * training time” to calculate the per capita income. The sum function finally help me to get the total income for the group.

This is where I have a problem : The IF that define the hourly rate doesn’t seem to be considered as an amount. When I use a lookup field on the other table, and activate the formula (“hourly rate * training time”) it juste gives me “ERROR!”. However it worked when I manually put the hourly rate using a “Currency” field. Any idea how to make this work ?

Hope it was clear enough, thanks anybody for your help :slightly_smiling_face:

[David_Skinner]

You need to make sure your formula field is recognised as a number. So it needs to be something like this simplified formula:

IF( {client}=“A”, 10, BLANK() )

Rather than

IF( {client}=“A”, “£10”, BLANK() )

Or

IF( {client}=“A”, 10, “” )

Every possibility in the formula must be a number, otherwise Airtable will format it as a string. You can tell whether it will work or not by clicking the formatting tab when setting up the formula. It should give you the option to format as a number, or currency. If it doesn’t then your formula is wrong.

[W_Vann_Hall]

Even better, let Airtable decide how to handle NULLs by leaving that part of the formula implicit:

IF({Client}='A',10)

An IF() function without an explicit ‘else’ branch defaults to a NULL value if the initial test evaluates to ‘false’. That avoids the chance of inadvertently casting the answer to an unintended data type.

[Max-Antoine_EDI]

Hi @David_Skinner

Thanks for the fast answer. I tried to remove the currency, my initial formula looked like this :

IF(
{Statut} = ‘SPE’,
‘21.50€’,
IF(
{Statut} = ‘AM’,
‘17.50€’
)
)

Even Without the currency it still display “ERROR!” :confused: I tried with “,” instead of “.” but changes nothing too. It’s weird because formatting option isn’t available as it doesn’t consider the result as a number.

[David_Skinner]

‘21.50€’ should be 21.5

[W_Vann_Hall]

Oops — now I’m going to contradict myself… :winking_face:

Lookup fields are passed as arrays — an array with a single element, possibly, but still an array.

To use the lookup field {Statut} in your comparison, you need to cast it as a string to compare it with a string. Try

IF(
    {Statut}&'' = 'SPE',
    '21.50€',
    IF(
        {Statut}&'' = 'AM',
        '17.50€'
        )
    )

[Max-Antoine_EDI]

@David_Skinner Made the try with various writings :

• 21.50€
• €21.50
• 21.50
• (all those propositions with “,” instead of “.”)
• 21.5
• 21.5€
• €21.5

None of it work T_T I’ve tried various formatting options on my formula field but it doesn’t change the “ERROR!” displaying on my per capita income.

It’s really weird because whatever I try, my IF formula (which is just suppose to give the hourly rate according to “Statut” field) doesn’t seem to be recognized as number or anything I could Format.

@W_Vann_Hall Thanks for the formula, doesn’t change the ERROR! message though :frowning:

[David_Skinner]

What happens if you put this formula in? Can you format it as a number? Make sure you have no inverted commas around the 21.5.

IF( {Statut} = “SPE”, 21.5, BLANK() )

[AlliAlosa]

Figured I’d throw this into the mix… I usually use ARRAYJOIN() when dealing with lookup fields.

SWITCH(ARRAYJOIN({Statut}), "SPE", 21.5, "AM", 17.5)

[Max-Antoine_EDI]

Hi @AlliAlosa

Thank you so much, it works as intended ! I’m not familiar with many formula’s function, just like ARRAYJOIN… It still bug me a little bit as it now work but I don’t feel like I truly understand why it didn’t work VS why it works now with your new function :grinning_face_with_big_eyes: Guess I need more practice with AIrtable.

Thank you so much @David_Skinner and @W_Vann_Hall too for taking the time to answer here.

[AlliAlosa]

Yay! You’re so welcome!

To put it very simply, values in lookup and rollup fields are treated as arrays, which are technically several values in one object.

Say your lookup field is displayed “A, B, C”. Internally, Airtable sees those values as “A”, “B”, and “C”. You need to cast the field to a string of characters in order to compare anything to “A, B, C”. @W_Vann_Hall gave a different approach to doing that in this reply.

The formula he gave is correct, but I’m guessing it gave you an error in your other fields because the quotes (around the numbers) and currency symbols cause the field to be read as a string instead of a number. If you remove those, his formula should work for you as well. :slightly_smiling_face:

[Max-Antoine_EDI]

Thanks for taking the time to explain this, makes more sense to me now.

To give a final word on this : effectively, @W_Vann_Hall formula works too if you withdraw the currency symbol and the quotes.

Community is wonderful here :slightly_smiling_face: