Using Lookup Value in Formula

Question

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.

ScottWorld · Accepted Answer

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.

Brian_Swichkow · Answer

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.

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!

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded