IF, IFERROR, SEARCH nested formula help

Options
Nataly Allimonos
Nataly Allimonos ✭✭✭
edited 04/06/23 in Formulas and Functions

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"))),"-")

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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")

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

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

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

  • Nataly Allimonos
    Nataly Allimonos ✭✭✭
    edited 04/09/23
    Options

    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:

    1. 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?
    2. 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
    Options

    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:

    1. How do I remove the dashes popping up in here? Extra dash somewhere in my formula?
    2. 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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Options

    @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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!