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?
Best 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
-
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!
-
Thank you didn't even think to use Index Match! It worked great!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!