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.