Return multiple values from all columns which meet criteria

[jen1392]

Hi all,

I have formulated columns Apple, Orange, Banana, and the formula produces "Y" or "N" values. I want to create another column that will return the name of the column if their value is "Y". Is there any way to do this?

I have tried nested IF formula, like IF(Apple="Y", "Apple", Orange="Y", "Orange"...), but the problem is they just return one value, but I want all the columns as long as their value is "Y".

Thanks in advance!

[TheTimeSavingCo · Accepted solution]

Try this:
 

IF(
  Apple = "Y",
  "Apple\n"
) & 
IF(
  Orange = "Y",
  "Orange\n"
) & 
IF(
  Banana = "Y",
  "Banana\n"
) 

[pressGO_design]

I keep this REGEX formula handy for cases like this:

REGEX_REPLACE(

  CONCATENATE(

    IF({A}, {A} & ", "),

    IF({B}, {B} & ", "),

    IF({C}, {C} & ", "),

    IF({D}, {D})

  ),

  ", $",

  ""

)

In your case, I think the concatenate part would look like

If(Apple = “Y”, “Apple, “),
If(Orange = “Y”, “Orange, “),
If(Banana = “Y”, “Banana, “),

PS & attribution: I got the formula from a solution that @kuovonne posted here.

[kuovonne]

Thanks for the credit for the formula. But it looks like you linked to my profile, and not where I describe the pattern, which is here.

[jen1392]

Hi all, thanks for the help! 

I'm not too familiar with using REGEX but the IF & formula works - thanks!

[amoersen]

Hi, I have a similar issue that I am trying to solve. I have twelve columns with various cost options that could be used for purchasing a product. I've created a column with drop downs that match the cost column descriptions. Is there a formula that could be used to select the cost option I'd like to proceed with and have it populate into a new cell? For example, if I choose to proceed with "PROTO LDP 0%- AIR/AIR" could I get the cost of $10 to populate into a new cell? 

[pressGO_design]

All of those columns would need to be formula columns, and then you would need to have IF statements in all of them that depend on the dropdown column. 

Or, if the number is static, you can create a “when a record is updated” automation with a bunch of conditions that puts a fixed number into the appropriate currency field.