Using lookup data in a formula

[Tonaya_Craft]

I am trying to use Lookup data in a formula and can’t get it to work.

Table A is a list of print products, and column 1 are the unit costs pulled from linked table B, which contains unit costs for different page counts.

In another column on Table A, I want to multiply the unit cost by 25. I have tried doing this using a forumla, and I have tried it using the Rollup function, and both result in “error.”

Also another weird thing I noticed: If I format the the column in table B that contains the unit costs as a number, the Lookup data in table A gets rounded up to a 1! So I can’t get any meaningful sums when I group the data. The only way to get the correct numbers to show up is if I leave the column in Table B formatted as short text.

Weird!

[Alex_Wolfe]

My first thought is to double check your lookup’s formatting and then see if the formula works (and also be sure your format of the formula is correct as well).

As far as the number formatting goes, you can specify the decimal places in the Field’s formatting options to prevent it from rounding.

[Tonaya_Craft]

So what I’ve figured out pretty much immediately is that this problem has to do with my aside: I have it formatted as text so of course I can’t use it in a formula!

And the other problem had to do with the way I had my formula formatted. Head smack!

[W_Vann_Hall]

Wrap your string with a VALUE() function and you can use it in your formula. :winking_face:

[John_Jacobs]

I am having an identical issue. My Table 1 has a number ‘X’, formatted as an integer (although I tried decimal as well, no difference). Table 2 looks up the value of X. Another field in Table 2 takes X and does a date add: DATEADD({date}, X, ‘day’). For whatever reason the formula is processed in such a way that the result always comes back of date - 1. I tried putting in a value() function around the date but that just created an error condition.

[John_Jacobs]

FYI, formatting has been triple checked, not an issue. I also tried the dateadd function from the same date with a hard coded number and it works fine. Something in the lookup process turns the lookup value not into a number, and the value() function does not correct it when nested inside of the formula, it only makes it worse, as stated, turning the result into an error.

[John_Jacobs]

fixed. It is you airtable, it is not me!!! I have a value that was put in as a number in table 1. I lookup a field in table 2 that corresponds to a value in table 1 and then attempt to add to a date, it does not work. I figure it is turned into a string, so I run the value() function on it, now my formula returns an error. So I force it to be a string by: table1value"&’’ (empty set string), then take the value of the string, so one formula that is: value(table1value&’’) and i finally get a usable number. A value should be either a number or a string, but for whatever reason, somehow the lookup function turns it into neither and needs to be forced into first a string, then the value of a string. THIS IS NOT GOOD BEHAVIOR AIRTABLE.

[kuovonne]

@John_Jacobs You have discovered that lookup fields are one of the most confusing fields to use in formulas. It sounds like you have found a workaround. However, for future reference, I recommend using a rollup field instead of a lookup field.

[John_Jacobs]

kuovonne:

You have discovered that lookup fields are one of the most confusing fields to use in formulas. It sounds like you have found a workaround. However, for future reference, I recommend using a rollup field instead of a lookup field.

thanks, i will check it out

[Lennart_Wittsto]

I ran into the same issue with the DATEADD formula and a looked-up value as the count.
An easier workaround is to multiply the looked-up value by 1.
At least that worked for me.