IF, IFERROR, SEARCH nested formula help

Nataly Allimonos · 04/06/23

Hi team -

Looking for help in either fixing the below formula or creating a new formula.

Goal behind the logic:

Be able to use this cell to confirm if another column,same row (Level) has the abbreviation "Mgr."

If it doesn't add a dash "-", full stop.

If it does, we need to search another column,same row (rep region) for the letters "AE", "RM", or "RS".

If the string has "RM" show "RM" in this cell.
If the string has "AE" show "AE" in this cell
Or If the string has "RS" show "RM" in this cell (yes, change to RM)

Chart: Blue columns is the data, orange column is where the formula will live.

Syntax: (Coming up as #unparseable)

=IF([Level]@row="Mgr",IFERROR(IF(SEARCH("RM",[Rep Region]@row,1),"RM"),IFERROR(IF(SEARCH("AE",[Rep Region]@row,1),"AE"),IF(SEARCH("RS",[Rep Region]@row,1),"RM"))),"-")

Paul Newcome · 04/11/23

Initially we pull the string out which gives us "RS", "RM", or "WG". You indicated that you wanted the "RS" converted to "RM" but the other two left alone. Since "WG" doesn't have the "S", we can just use a SUBSTITUTE function to swap the "S" for the "M" which saves us a bit of typing instead of trying to use a bunch of nested IFs.

=SUBSTITUTE("RM", "S", "M") will output "RM" (no "S" to swap out)

=SUBSTITUTE("RS", "S", "M") will output "RM" (swaps the "S" out for the "M")

=SUBSTITUTE("WG", "S", "M") will output "WG" (no "S" to swap out)

In the above, we drop the MID statement in place of the "letters" to grab the letters dynamically.

=SUBSTITUTE(MID(..........), "S", "M")

Write our single IF for Level@row

=IF(Level@row = "Mgr", SUBSTITUTE(MID(..........), ..........), "-")

Then wrap the whole thing in the IFERROR to output "RM" for any "problem rows".

=IFERROR(IF(.........., SUBSTITUTE(MID(..........), ..........), ..........), "RM")

Paul Newcome · 04/07/23

There is no SEARCH function in Smartsheet. Try this instead:

=IF(Level@row = "Mgr", SUBSTITUTE([Rep Region]@row, "S", "M"), "-")

Nataly Allimonos · 04/09/23

Thanks @Paul Newcome ! This helps a lot to know and simplifies a bit. Based on this, the question became: "how can I take this [Rep Region] column and extract only the "Role" part of the string?" I used this discussion post for my answer and it seemed like I got it until I noticed that a few rows are coming up as "#Invalid Value."

Here's what I have:

This one is popping up as #Invalid Value." It seems to me that these are coming up as invalid value bc the majority of these strings are:

Dept-Region-Country-Office-Corporate-Vertical-Role-Mgr (this is what I based my formula off of)

and these invalid strings are:

Dept-Region-Country-Office-Corporate-Vertical (data set is pretty large, didn't realize these were a thing)

For the #Invalid data string - The ideal is to have those (maybe?) nested into an IF formula.

Logic would be: If there is an error (#invalid data) simply show as "RM." Tried a few IFs and IFERRORs but nothing has worked yet. Any guidance on this?

Bonus Questions:

I have a "-" coming up as the first character in my formula column, and wondering if there's an extra "-" in my formula that I'm missing?
~~For the "-RM-MGR" output in the formula column, how can I make the formula stop at the role portion of the string and not capture the last portion MGR?~~ Solved this question

Nataly Allimonos · 04/09/23

Follow up:

I have a new formula with IF statements that seems to be solving for the above

=IF(MID([Rep Region]@row, FIND("~", SUBSTITUTE([Rep Region]@row + "-", "-", "~", 6)), FIND("~", SUBSTITUTE([Rep Region]@row + "-", "-", "~", 6)) - FIND("~", SUBSTITUTE([Rep Region]@row + "-", "-", "~", 5))) = 0, "RM", MID([Rep Region]@row, FIND("~", SUBSTITUTE([Rep Region]@row + "-", "-", "~", 6)), FIND("~", SUBSTITUTE([Rep Region]@row + "-", "-", "~", 6)) - FIND("~", SUBSTITUTE([Rep Region]@row + "-", "-", "~", 5))))

The only Qs I need help on to really finish this off:

How do I remove the dashes popping up in here? Extra dash somewhere in my formula?
I have data populating as "RS" and "WG" - formula is reading these right however, I would love to add a 2nd IF statement that says if this is what's populating change it to "RM."

Paul Newcome · 04/10/23

My apologies. I believe I may have answered before the screenshot was added and didn't know we were working with text strings.

This should simplify things as well as get rid of that dash.

=IFERROR(IF(Level@row = "Mgr", SUBSTITUTE(MID([Rep Region]@row, FIND("~", SUBSTITUTE([Rep Region]@row, "-", "~", 6)) + 1, 2), "S", "M"), "-"), "RM")

Nataly Allimonos · 04/10/23

@Paul Newcome - That simplifies soo much! Thank you! For the intial question, this is exactly what I needed!

Wondering what the "S" and "M" parts are calling in this formula?

Paul Newcome · 04/11/23

Initially we pull the string out which gives us "RS", "RM", or "WG". You indicated that you wanted the "RS" converted to "RM" but the other two left alone. Since "WG" doesn't have the "S", we can just use a SUBSTITUTE function to swap the "S" for the "M" which saves us a bit of typing instead of trying to use a bunch of nested IFs.

=SUBSTITUTE("RM", "S", "M") will output "RM" (no "S" to swap out)

=SUBSTITUTE("RS", "S", "M") will output "RM" (swaps the "S" out for the "M")

=SUBSTITUTE("WG", "S", "M") will output "WG" (no "S" to swap out)

In the above, we drop the MID statement in place of the "letters" to grab the letters dynamically.

=SUBSTITUTE(MID(..........), "S", "M")

Write our single IF for Level@row

=IF(Level@row = "Mgr", SUBSTITUTE(MID(..........), ..........), "-")

Then wrap the whole thing in the IFERROR to output "RM" for any "problem rows".

=IFERROR(IF(.........., SUBSTITUTE(MID(..........), ..........), ..........), "RM")

Formulas and Functions

Formulas and Functions

IF, IFERROR, SEARCH nested formula help

Best Answer

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Counting occurrences of a string in a row

Rounding to nearest Thursday in the past or current day if it's Thursday

JOIN function in a Sheet Summary