Pull Unique Value into New Column?

One point that’s a little fuzzy with your description is exactly what you want to be in each of the separate Low/Medium/High fields (columns). You say:

…but does that mean

1. ALL instances of each Low/Medium/High are tallied, so that a single Low field (column) entry could be, based on your example, “Low, Low, Low, Low”; i.e. “all of the lows” meaning literally all of the lows? Or
2. Only one Low/Medium/High entry in each respective field depending on whether or not that choice was selected at least once in the lookup?

The solution will depend on which way you want to go.

If it’s #1, you could write a formula with a bunch of nested SUBSTITUTE functions that progressively strip out the bits you don’t want in each field by replacing them with empty strings. First off, though, it’s important to note that while Airtable displays the Lookup field results as a comma-separated list of items, that’s just for display. If you add a formula field with the following:

{Lookup} & ""

Your example above would output:

LowLowMediumLowHighMediumLow

This actually makes your work easier because you don’t have to remove the commas, only the unwanted levels. However, you probably want the end result with the commas in place, which is actually easy to do by taking advantage of the case-sensitivity of the level names. For example, with the Low level, your final substitution would replace “wL” with “w, L”, meaning that the comma-space separators would only appear between words, and never at the end. Similar substitutions could be done for the others.

Here’s the final formula for the Low field (replace {Lookup} with your actual Lookup field name):

SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(
            {Lookup} & "", "High", ""
        )
        , "Medium", ""
    )
    , "wL", "w, L"
)

…and the Medium field:

SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(
            {Lookup} & "", "High", ""
        )
        , "Low", ""
    )
    , "mM", "m, M"
)

…and the High field:

SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(
            {Lookup} & "", "Low", ""
        )
        , "Medium", ""
    )
    , "hH", "h, H"
)

Here’s how it looks in a quick test table I built:

However, if #2 is your goal and you simply want to know if Low (or Medium or High) exists anywhere in the Lookup values, then your field formulas would be more like this (example with the Low field only, and using an emoji as an indicator):

IF(FIND("Low", {Lookup} & ""), "✅")

@Justin_Barrett Thanks for the response! To answer your question, my end goal is to be able to get a “count” or tally of the number of lows, mediums and highs. The screenshots of the table is exactly what I am attempting to do.

So, my thought (and since I am very new to airtable), was to be able to “bring” all of the lows, mediums and highs into their own separate new columns, and essentially use the count functionality in an additional new column next to it. I would then probably just hide some columns to clean things up.

Edit* I tried your formulas and was not able to execute. The error is “unknown field names:lookup”. Any ideas? I think my issue lies in the lookup formula you reference prior to using the substitute formula.

My apologies as well…im very new to airtable. I am trying to post some screenshots, but its not allowing me to. I will keep trying.

As I pointed out, “Lookup” is just the field name I used in my test. Replace that with the name of the lookup field in your own table. Also remember that field names are case-sensitive, so “lookup” and “Lookup” are viewed differently.

If a count of the resulting values is your final goal, the count functions won’t get you there. However, a small modification to the formulas above will work. Instead of adding commas between each level name as the final substitution, convert the name into a single character (any character will do), then use LEN to count those characters. Here are the above field formulas converted to operate that way:

LEN(
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(
                {Lookup} & "", "High", ""
            )
            , "Medium", ""
        )
        , "Low", "X"
    )
)

LEN(
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(
                {Lookup} & "", "High", ""
            )
            , "Low", ""
        )
        , "Medium", "X"
    )
)

LEN(
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(
                {Lookup} & "", "Low", ""
            )
            , "Medium", ""
        )
        , "High", "X"
    )
)

LEN(
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(
                {Lookup} & "", "High", ""
            )
            , "Medium", ""
        )
        , "Low", "X"
    )
)

LEN(
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(
                {Lookup} & "", "High", ""
            )
            , "Low", ""
        )
        , "Medium", "X"
    )
)

LEN(
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(
                {Lookup} & "", "Low", ""
            )
            , "Medium", ""
        )
        , "High", "X"
    )
)

Thanks! Hope you can maybe help me with one other thing regarding the above formula. Is there a way to combine Low, Medium, and High in that IF/Find formula?

For example, say in column A, the first cell has low, the second cell has medium and the third cell has high. Can I write the formula for column B to say essentially look at column A, if low, return low, if medium, return medium, if high, return high?

I’ve already replaced the “check mark” with the actual value, so I have that part.

Thanks again!

I’m not sure I understand your request. Part of my confusion is your mixed usage of “column” and “cell.” Columns in Airtable are fields, and each field of a given record is somewhat similar to a cell in a spreadsheet. However, you said,

Because a column is a field, and a cell is (essentially) also a field, I’m not sure how to interpret that description. Could you please break down your setup in a bit more detail?

How would one get it to just say a single instance of “Low” if the cell had “Low, Low, Low, Low”?

If you want the lone “Low” in a separate field from the collection, you would make a formula field with this formula:

ARRAYUNIQUE({field name})

The same would also work as the aggregation function in a Rollup, but you’d use values in place of {field name}.