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:

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