Help

IF Function & formula's using lookup

3729 11
cancel
Showing results for 
Search instead for 
Did you mean: 
Max-Antoine_EDI
5 - Automation Enthusiast
5 - Automation Enthusiast

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:

11 Replies 11

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.

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.

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.

‘21.50€’ should be 21.5

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
5 - Automation Enthusiast
5 - Automation Enthusiast

@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:

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
10 - Mercury
10 - Mercury

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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.