Skip to main content

Produce string of results if multiple nested if statements are true

  • February 2, 2022
  • 1 reply
  • 11 views
A
Forum|alt.badge.img+1

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 topic has been closed for replies.

1 reply

Ben_Young1
Forum|alt.badge.img+22
  • Ben_Young1
  • Brainy
  • February 8, 2022

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.

Terms & ConditionsAccessibility statement