Best way to set-up an index contains

Megan_Perry_Thibault

Hello Smartsheet,

I have a sheet with URLs from our website that I need to be able to flag by division. To do this I have set-up a master sheet with the division's name and base URLs. What I need now is a formula to grab the division names from the master sheet. My intent was to set it up as an INDEX(MATCH()) but I realize now that the match function needs the values to be identical (I think at least).

So, what is the best way to grab the division names from the master sheet? Because the links I have are not "companyurl.com/division/example" (which is what is on my master sheet as the root URL) they are "companyurl.com/division/example/page1", "companyurl.com/division/example/sample" etc.

Unfortunately, the division URL's don't even follow the same structures (some are companyurl.com/division/sample and others are companyurl.com/abc123) which has made some of my initial thoughts go right out the window.

(For anyone wondering, the list will be getting updated/added to as we find more pages that the divisions need to make corrections/updates to, hence why we need to be able to break them down by division rather than just let them all hangout in huge list our web authors would then have to parse through).

Paul Newcome

Give this a try:

=INDEX(COLLECT({Reference Sheet Primary Column}, {Reference Sheet Base URL Column}, CONTAINS(@cell, URL@row)), 1)

Cory Page

Hello, could you use index/match with the Left formula? rather than matching the entire URL you may be able to just grab the first 10 or so characters and use that to match to your master sheet. Not sure if the start of the URL is the same as the start of your master sheets names.

Paul Newcome

Are you able to provide some screenshots (with sample data) for reference?

Megan_Perry_Thibault

@Paul Newcome Sure, see attached.

Hopefully these help make a little more sense with what I was saying. The video pages column that says Division is where I need the names (the Primary Column of Section URLs) to show up.

Paul Newcome

Give this a try:

=INDEX(COLLECT({Reference Sheet Primary Column}, {Reference Sheet Base URL Column}, CONTAINS(@cell, URL@row)), 1)

Megan_Perry_Thibault

https://community.smartsheet.com/discussion/comment/387394#Comment_387394

Ha! It works now. Thank you!!

Paul Newcome

Happy to help. 👍️