Using Lookup Value in Formula

I’m trying to use data from a lookup field in a formula and keep getting #ERROR!.

The formula (below) works with a plain text field created to test, but not the lookup field.

IF({CITIZEN SLACKIDS}=BLANK(),’’,

‘<@’ & SUBSTITUTE(VALUE({CITIZEN SLACKIDS}),’, ', ‘>, <@’) & ‘>’)

I’ve tried wrapping the lookup field in VALUE () to no avail.

Hmmm… that’s a bit tricky, because those commas don’t really exist in your lookup field. Your lookup field is an array that Airtable is visually separating with commas.

I’m sure there are probably several different ways to solve this dilemma, but here’s one way that you can do it:

In your OTHER table (not this table), create a formula field which is this:

"<@" & CITIZEN SLACKIDS & ">"

Then, back in THIS table, change your lookup field to lookup that formula field instead.

1 Like

Thank you, Scott!

Your lookup field is an array that Airtable is visually separating with commas.

SUPER helpful to know, for this and other bits.

Created the formula in the other table as recommended and got the desired result (below). It’s a bit clunky by a necessity I now understand much better. Thank you!

Screen Shot 2021-01-19 at 11.39.08 AM

Screen Shot 2021-01-19 at 11.40.53 AM

1 Like

Fantastic! Glad I could help! :slight_smile:

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.