Skip to main content

Hi All!


I have a nested IF statement that is set up to find if the value from Column A, B, C, D, or E matches Column F. If one matches, it produces the name of the column. What I’m looking for is a way to produce more than one name if more than one column matches. Currently it looks like this:


IF(

OR(

ColumnA, ColumnB, ColumnC, ColumnD, ColumnE

),

IF(

ColumnA=ColumnF,“ColumnA”,

IF(

ColumnB=ColumnF,“ColumnB”,

IF(

ColumnC=ColumnF,“ColumnC”,

IF(

ColumnD=ColumnF,“ColumnD”,

IF(

ColumnE=ColumnF,“ColumnE”,

)

)

)

)

)

)


This only gives me one result. If more than one column matches, I’d like it to somehow give me both or all results


Thanks!

This post is kinda old, but I had a bit of fun tweaking your formula around and came out with this:


IF(

   OR(

       {Column A},

       {Column B},

       {Column C},

       {Column D},

       {Column E}

   ),



   (

       IF(

           {Column A} = {Column F},

           {Column A},

           ""

       ) &

       IF(

           {Column B} = {Column F},

           " " & {Column B},

           ""

       ) &

       IF(

           {Column C} = {Column F},

           " " & {Column C},

           ""

       ) &

       IF(

           {Column D} = {Column F},

           " " & {Column D},

           ""

       ) &

       IF(

           {Column E} = {Column F},

           " " & {Column E},

           ""

       )

   )

)



This was a nice challenge!


Here’s what I tweaked.

Instead of nesting a bunch of IF statements together, I separated them and concatenated possible values. I like this modular structure for a few reasons.


If a problem occurred with one of the fields, or if you wanted to remove, add, or modify a set of fields in the future, you won’t have to dig your hands into the guts of a huge nested mess to try and plug in a new field or configuration.

Reply


Terms & Conditions