Nested If OR formula, Better way?

Jeremy Oesch

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?

Scott Orsey

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!

Scott Orsey

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!

Jeremy Oesch

@Scott Orsey,

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