Multiple 'If/Contains' Formula
Hi!
I am compiling a list of IP addresses that are associated with phyical sites. I am able to create a basic IF/Contains formula to populate the 'Location' column based on the 'IPv4' row.
Issue:
The issue I am running into is that I have many locations. If i keep the list small (20/30 locations), the formula works wonderfully. However, as I attempt to capture more granular location information, the formula becomes too big (65 sites, estimated 4,200 characters) & Smartsheet truncates the forumla.
Example:
IPv4 Location
192.168.1 New York City
192.168.2 Toronto
192.168.3 <Formula>
Sample formula I am using:
=IF(CONTAINS("192.168.1", [IPv4 Address]@row), "New York", IF(CONTAINS("192.168.2", [IPv4 Address]@row), "Toronto", <next IP>, <next IP>)
I believe I am reaching the limit of Smartsheet formula.
Is there an alternative approach to this?
Thank you in advance for any guidance & assistance with this.
Answers
-
You can use Index/Match function. Have the IP and Location in a separate sheet.
...
-
Index/Match was my first thought as well. Because (I am assuming) you are looking at multiple possible addresses that vary only in the last octet, you will have to use LEFT to take only the digits prior to the last decimal. Because the last octet can be 1, 2, or 3 digits, you will also likely need to use a FIND/REPLACE to dynamically determine which digits to use.
-
Try Index/Collect/Contains - here is a similar post.
https://community.smartsheet.com/discussion/88554/index-match-based-on-partial-data
...
-
Thank you for your input. Would it possible you can share a sample formula for me to understand/plan around with?
-
@heyjay & @Carson Penticuff, thank you both for your input.
I have made some progress via INDEX/MATCH.
My current formula:
=INDEX(Location:Location, MATCH(LEFT([Test-IPv4]@row, 8), [IPv4]:[IPv4]))
One scenario that is messing me up is when the IP addresses don't follow a pattern.
Example:
I got a few IPs as:
10.4.65.68
While others:
10.130.45.34
How do I go about tackling those?
It would be great if its all one formula, if not, I can do seperate formulas for the unique IPs.
Thanks again for any assistance/guidance!
-
To provide further clarity, the location is dependent on the 2nd octect in my examples.
10.4.x.x = Ohio
10.120.x.x = New York
10.40.x.x = California
Thank you!
-
Hi Folks,
I need still assistance with this.
I am able to achieve the results I need but when the range I am searching within changes from a single digit to triple digit, the formula doesn't work:
As you can see, any results with 10.1.200.x or 10.1.250.x are not correct.
=INDEX([IP-Location]:[IP-Location], MATCH(LEFT([User IP]@row, 9), IP:IP))
Thanks in advance.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!