Feb 01, 2022 07:42 PM
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!
Feb 08, 2022 10:14 AM
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.