Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Return multiple values from all columns which meet criteria

Topic Labels: Formulas
Solved
Jump to Solution
1735 6
cancel
Showing results for 
Search instead for 
Did you mean: 
jen1392
4 - Data Explorer
4 - Data Explorer

Screenshot 2024-02-26 at 4.02.13 PM.png

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!

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

Try this:

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

Screenshot 2024-02-26 at 4.48.36 PM.png

 

See Solution in Thread

6 Replies 6
TheTimeSavingCo
18 - Pluto
18 - Pluto

Try this:

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

Screenshot 2024-02-26 at 4.48.36 PM.png

 

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.

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
4 - Data Explorer
4 - Data Explorer

Hi all, thanks for the help! 

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

amoersen
4 - Data Explorer
4 - Data Explorer

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? 

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.