Searching Field for Contents of Named Range

excel_ga

New Member
Joined
Feb 13, 2017
Messages
10
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

<colgroup><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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
 

excel_ga

New Member
Joined
Feb 13, 2017
Messages
10
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.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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&" ")))
 

Forum statistics

Threads
1,089,421
Messages
5,408,137
Members
403,186
Latest member
123hpeinstall

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top