Multiple 'If/Contains' Formula

PerDeSi

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.

heyjay

You can use Index/Match function. Have the IP and Location in a separate sheet.

Carson Penticuff

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.

heyjay

Try Index/Collect/Contains - here is a similar post.

https://community.smartsheet.com/discussion/88554/index-match-based-on-partial-data

PerDeSi

https://community.smartsheet.com/discussion/comment/445884#Comment_445884

Thank you for your input. Would it possible you can share a sample formula for me to understand/plan around with?

PerDeSi

@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!

PerDeSi

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!

PerDeSi

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.