Airtable Cobuilder is here! Learn more about our new no-code app creation feature, powered by AI on the Airtable Academy

# Nested Formula Help

Topic Labels: Formulas
Solved
Jump to Solution
962 2
cancel
Showing results for
Search instead for
Did you mean:
5 - Automation Enthusiast

This is probably so easy, but I just can’t get it to work.

I hav 13 columns that are using a formula to extrapolate a comma separated list from a single column. That works great.

I am then trying to use a new formula field to look across all 13 and put the result of the last column that is not blank.

(i.e. if column 1 has text then check column 2. If column 2 is blank then column 1 in field if column 2 has text then check column 3 and if 3 is blank them column 2 and so on.

the columns are called item1, item2, item3 and so on.

Thanks in advance

1 Solution

Accepted Solutions
18 - Pluto

Instead of starting from the beginning of the list, try a formula that starts at the end of the list.

``````IF(item13, item13,
IF(item12, item12,
IF(item11, item11,
IF(item10, item10,
IF(item9, item9,
IF(item8, item8,
IF(item7, item7,
IF(item6, item6,
IF(item5, item5,
IF(item4, item4,
IF(item3, item3,
IF(item2, item2,
IF(item1, item1
)))))))))))))
``````
2 Replies 2
6 - Interface Innovator

Hi Jason,

Interesting challenge. I’m operating on the logic that each successive item can only be filled if the one before is filled first, (ie. item3 has to be filled before item4 is filled.)

In that case, I think this is what you need. (Totally untested).

``````IF({Item1},IF({Item2},IF({Item3},IF({Item4},IF({Item5},IF({Item6},IF({Item7},IF({Item8},IF({Item9},IF({Item10},IF({Item11},IF({Item12},IF({Item13},"ALL ITEMS FILLED",{Item12}),{Item11}),{Item10}),{Item9}),{Item8}),{Item7}),{Item6}),{Item5}),{Item4}),{Item3}),{Item2}),{Item1}),"NO ITEMS")
``````

It’s a messy one I’m not sure how to make pretty. But here is what’s being combined:

``````IF({Item1},notblank,"NO ITEMS")
IF({Item2},notblank,{Item1})
IF({Item3},notblank,{Item2})
IF({Item4},notblank,{Item3})
IF({Item5},notblank,{Item4})
IF({Item6},notblank,{Item5})
IF({Item7},notblank,{Item6})
IF({Item8},notblank,{Item7})
IF({Item9},notblank,{Item8})
IF({Item10},notblank,{Item9})
IF({Item11},notblank,{Item10})
IF({Item12},notblank,{Item11})
IF({Item13},"ALL ITEMS FILLED",{Item12})
``````

In each line, looking if an item is blank, and if it is then it resolves the text from the previous item. If it’s ‘notblank’ then continues to the next IF statement. If the first item is empty, it should say “NO ITEMS” and if all the items are filled out, it should say “ALL ITEMS FILLED”

Again, totally untested, but hopefully helpful :slightly_smiling_face:

18 - Pluto

Instead of starting from the beginning of the list, try a formula that starts at the end of the list.

``````IF(item13, item13,
IF(item12, item12,
IF(item11, item11,
IF(item10, item10,
IF(item9, item9,
IF(item8, item8,
IF(item7, item7,
IF(item6, item6,
IF(item5, item5,
IF(item4, item4,
IF(item3, item3,
IF(item2, item2,
IF(item1, item1
)))))))))))))
``````