Produce string of results if multiple nested if statements are true

[April_Kastner]

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!

[Ben_Young1]

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.