Multiple 'If/Contains' Formula

PerDeSi · 07/22/24

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 · 07/22/24

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

Carson Penticuff · 07/22/24

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 · 07/22/24

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

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

PerDeSi · 07/24/24

Carson Penticuff

Jul 22, 2024

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.

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

PerDeSi · 08/10/24

@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 · 08/10/24

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 · 09/20/24

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.

Formulas and Functions

Formulas and Functions

Multiple 'If/Contains' Formula

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Need to update dates to Q1_2025, Q2_2025, Q3_2025, Q4_2025

Writing a reference formula with multiple IF/AND situations

How to return lowest date when index matching complete columns rather than just a selection?