Skip to main content
Solved

Formula to return multiple values

  • June 21, 2023
  • 4 replies
  • 62 views
L
Forum|alt.badge.img+4
  • lcar
  • New Participant
  • 3 replies

I have data that essentially looks like this:

I want to use a formula to populate the last column with the type (in my example, food) based on whether or not other cells in the row are greater than 0. So IF (column A, B or C is >0, return "Fruit") AND IF (Column D>0, return "Fungus" and so on). I can get it to return one of the types but not all. Ideally they would be separated by a comma.

I also sometimes have a blank cell and sometimes I have a 0. I know I can do a simple IF statement and have columns for each type, but is there a way to do this in one column?  

Thank you for your patience, I am new to airtable and airtable formulas!

Best answer by Stephen_Orr1

Thank you @Stephen_Orr1 ! That is close. I slightly changed your formula to give me "fruit" rather than "apples" and "vegetables" rather than "carrots" and so on. So consequently if I have 4 applies and 2 bananas say, the output gives me: "Fruit, Fruit" and I only need it written once. Any way to get around that?


Here you go 🙂

REGEX_REPLACE( IF(OR({Apples}, {Bananas}, {Limes}), 'Fruit, ', '') & IF(OR({Mushrooms}, {Carrots}, {Squash}, {Ginger}), 'Vegetables, ', '') & IF(OR({Rosemary}, {Thyme}), 'Herbs, ', '') , '[, ]*$', '' )

4 replies

S
Forum|alt.badge.img+18
  • Stephen_Orr1
  • Inspiring
  • 272 replies
  • June 21, 2023

Hi @lcar,

Try this

REGEX_REPLACE( IF({Apples}, 'Apples, ', '') & IF({Bananas}, 'Bananas, ', '') & IF({Limes}, 'Limes, ', '') & IF({Mushrooms}, 'Mushrooms, ', '') & IF({Carrots}, 'Carrots, ', '') & IF({Squash}, 'Squash, ', '') & IF({Ginger}, 'Ginger, ', '') & IF({Rosemary}, 'Rosemary, ', '') & IF({Thyme}, 'Thyme, ', '') , '[, ]*$', '' )

 -Stephen

L
Forum|alt.badge.img+4
  • lcar
  • Author
  • New Participant
  • 3 replies
  • June 21, 2023

Hi @lcar,

Try this

REGEX_REPLACE( IF({Apples}, 'Apples, ', '') & IF({Bananas}, 'Bananas, ', '') & IF({Limes}, 'Limes, ', '') & IF({Mushrooms}, 'Mushrooms, ', '') & IF({Carrots}, 'Carrots, ', '') & IF({Squash}, 'Squash, ', '') & IF({Ginger}, 'Ginger, ', '') & IF({Rosemary}, 'Rosemary, ', '') & IF({Thyme}, 'Thyme, ', '') , '[, ]*$', '' )

 -Stephen


Thank you @Stephen_Orr1 ! That is close. I slightly changed your formula to give me "fruit" rather than "apples" and "vegetables" rather than "carrots" and so on. So consequently if I have 4 applies and 2 bananas say, the output gives me: "Fruit, Fruit" and I only need it written once. Any way to get around that?

S
Forum|alt.badge.img+18
  • Stephen_Orr1
  • Inspiring
  • 272 replies
  • Answer
  • June 21, 2023

Thank you @Stephen_Orr1 ! That is close. I slightly changed your formula to give me "fruit" rather than "apples" and "vegetables" rather than "carrots" and so on. So consequently if I have 4 applies and 2 bananas say, the output gives me: "Fruit, Fruit" and I only need it written once. Any way to get around that?


Here you go 🙂

REGEX_REPLACE( IF(OR({Apples}, {Bananas}, {Limes}), 'Fruit, ', '') & IF(OR({Mushrooms}, {Carrots}, {Squash}, {Ginger}), 'Vegetables, ', '') & IF(OR({Rosemary}, {Thyme}), 'Herbs, ', '') , '[, ]*$', '' )
L
Forum|alt.badge.img+4
  • lcar
  • Author
  • New Participant
  • 3 replies
  • June 21, 2023

Yes that's perfect, thank you @Stephen_Orr1 !


Terms & ConditionsAccessibility statement