Nested If OR formula, Better way?

Hello,

I am writing out a =If(or)) function right now and it will be way to long if i do it step by step and i am wondering if there is a better way to do this. So my formula is =if(or(Location@row="Place 1",Location@row="Place 2"), "USA",if(or))... I have about 150 places i need to attach to 20 different countries. Is there a faster way to do this or am i doing this right?

Tags:

Best Answer

  • Scott Orsey
    Scott Orsey ✭✭✭✭✭
    Answer ✓

    Hi @Jeremy Oesch , If you try to continue with 150 locations you are going to exceed the character limits for a cell in Smartsheet. A better way to do this is to create a second lookup sheet where you list all of your locations in one column and the country in a second column. Then you'll use a cross sheet reference to look up your location and return the country using INDEX(MATCH()) structure like the following:

    = INDEX({Column of countries from lookup sheet},MATCH(Location@row,{Column of locations from lookup sheet},0))

    If you've never used cross-sheet references before, you may want to do a little research. Also, there are a lot of examples in the Community using INDEX/MATCH.

    This approach also has the advantage of being easy to add additional locations without changing your formulas.

    Hope this helps. Be well!

    If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!

Answers

  • Scott Orsey
    Scott Orsey ✭✭✭✭✭
    Answer ✓

    Hi @Jeremy Oesch , If you try to continue with 150 locations you are going to exceed the character limits for a cell in Smartsheet. A better way to do this is to create a second lookup sheet where you list all of your locations in one column and the country in a second column. Then you'll use a cross sheet reference to look up your location and return the country using INDEX(MATCH()) structure like the following:

    = INDEX({Column of countries from lookup sheet},MATCH(Location@row,{Column of locations from lookup sheet},0))

    If you've never used cross-sheet references before, you may want to do a little research. Also, there are a lot of examples in the Community using INDEX/MATCH.

    This approach also has the advantage of being easy to add additional locations without changing your formulas.

    Hope this helps. Be well!

    If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!

  • @Scott Orsey,

    Thank you didn't even think to use Index Match! It worked great!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!