# Return multiple values from all columns which meet criteria

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

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
18 - Pluto

Try this:

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

6 Replies 6
18 - Pluto

Try this:

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

10 - Mercury

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.

18 - Pluto

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.

4 - Data Explorer

Hi all, thanks for the help!

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

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?

10 - Mercury

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.