+ Reply to Thread
Results 1 to 4 of 4

Formula Q: matching a text value against multiple text-strings (in different cells).

  1. #1
    Registered User
    Join Date
    01-31-2012
    Location
    UK
    MS-Off Ver
    2007
    Posts
    1

    Formula Q: matching a text value against multiple text-strings (in different cells).

    Hi all - apologies for the less than helpful title. Hopefully the below will make more sense.

    I am trying to identify whether a text string in one cell contains the whole text another cell. In my example, the text string I am testing is a URL (e.g. www.website.com/example/) and I am comparing it against a long list of domain names (e.g. mysite.com, yoursite.com, website.com) to see if there is a match.

    In the example above, if the URL contained either mysite.com, yoursite.com or website.com, the output would be 'Yes'.

    Please see the attachment. The URL list is in the URL tab, column A, with output in column B (and what its supposed to come out as in columnD). The domain list to compare against is on the domain list tab, column A.

    I've dug around, and come up with this..=IF(SUM(NOT(ISERROR(FIND('Domain List'!B2:B19,A2)))*1)>0,YES)... but unnsurprisingly, it doesn't work.

    Anyone understand what I'm trying to do? Anyone got a clue how to do it?

    Anyone? Anyone? Bueller? Anyone?

    Rob
    Attached Files Attached Files
    Last edited by rpell; 01-31-2012 at 12:27 PM. Reason: because I don't read the rules... :(

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Juicy formula question / are you a genius?

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    One one is using an array formula, commit using CTRL+SHIFT+ENTER, in URL!B2
    =IF(MAX(IFERROR(FIND(Domains!$A$2:$A$19,URL!A2),0))>0,"Yes","No")

    Copy down to B9
    Cheers
    Andy
    www.andypope.info

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Juicy formula question / are you a genius?

    Your formula amost works, I tweaked it to this

    =IF(SUM(NOT(ISERROR(FIND(Domains!A$2:A$19,A2)))*1)>0,"yes","no")

    and that works with "array entry", i.e. confirmed with CTRL+SHIFT+ENTER......but I prefer this version

    =IF(COUNT(SEARCH(Domains!A$2:A$19,A2)),"yes","no")

    ...also confirmed with CTRL+SHIFT+ENTER

    Domains!A$2:A$19 shouldn't contain blanks.....

    ...or this non-array version will allow blanks too

    =IF(SUMPRODUCT(ISNUMBER(SEARCH(Domains!A$2:A$19,A2))*(Domains!A$2:A$19<>"")),"yes","no")

    SEARCH does the same as FIND except it's not case-sensitive, switch back to FIND if you want case-sensitive matches only
    Last edited by daddylonglegs; 01-31-2012 at 12:35 PM.
    Audere est facere

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Formula Q: matching a text value against multiple text-strings (in different cell

    Alternative way:
    Please Login or Register  to view this content.
    Confirmed with Ctrl-Shift-Enter
    Quang PT

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1