Searching Field for Contents of Named Range
Results 1 to 4 of 4

Thread: Searching Field for Contents of Named Range
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Searching Field for Contents of Named Range

    I am using the following formula to check if a contact's job title, stored in cell K5, is found in a named range of job titles.

    SUMPRODUCT(--ISNUMBER(SEARCH(LeadershipTitles,K5)))>0

    The problem is that this formula returns TRUE when the value in K5 is "Sales Coordinator" but that text (i.e., "Sales Coordinator") is not present in the named range LeadershipTitles (contents of that range listed below).

    Any ideas?

    Leadership Titles
    C.E.O.
    C.F.O.
    CCO
    CEO
    CF)
    CFO
    Chief Bottle Washer
    COO
    EPO
    EVP
    Executive Director
    Executive Manager
    Executive Sales
    Founder
    Genearl Manager
    General Manager
    GM
    Managing Director
    Managing Partner
    Officer
    Owner
    Partner
    President
    Prez.
    Principal
    V.P.
    V/P
    VP

  2. #2
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Searching Field for Contents of Named Range

    Hi,

    A couple of things:

    1. You have your SEARCH criteria backwards, right now you're searching LeadershipTitles within K5, and the 8th one down the list is COO, which IS part of Sales Coordinator.

    Your formula should be:

    =SUMPRODUCT(--ISNUMBER(SEARCH(K5,LeadershipTitles)))>0

    But, why not just use COUNTIF:

    =COUNTIF(LeadershipTitles,K5)>0

  3. #3
    New Member
    Join Date
    Feb 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Searching Field for Contents of Named Range

    Quote Originally Posted by jtakw View Post
    Hi,

    A couple of things:

    1. You have your SEARCH criteria backwards, right now you're searching LeadershipTitles within K5, and the 8th one down the list is COO, which IS part of Sales Coordinator.

    Your formula should be:

    =SUMPRODUCT(--ISNUMBER(SEARCH(K5,LeadershipTitles)))>0

    But, why not just use COUNTIF:

    =COUNTIF(LeadershipTitles,K5)>0


    Thanks very much for pointing out the issues with the formula and for catching the "COO" -- I missed that.

    The one challenge I have is that the LeadershipTitles list is not a list of exact titles but rather it contains portions of job titles that I want to check for a partial match. For example, I want to check the K5 cell and if it is equal to "Vice President Europe", I would want the formula to evaluate to TRUE because the LeadershipTitles list contains "Vice President" in it. Will the formula as corrected above do that?

    Thanks again.

  4. #4
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Searching Field for Contents of Named Range

    If that's the case, assuming you're Only trying to match Whole words or Phrases in LeadershipTitles against K5, try either of the following:

    =SUMPRODUCT(--ISNUMBER(SEARCH(" "&LeadershipTitles&" "," "&K5&" ")))>0

    OR

    =ISNUMBER(LOOKUP(2,1/SEARCH(" "&LeadershipTitles&" "," "&K5&" ")))

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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