Nested If OR formula, Better way?

Options

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:

• ✭✭✭✭✭
Options

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.

Hope this helps. Be well!

• ✭✭✭✭✭
Options

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.

Hope this helps. Be well!

• Options

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!