Searching Field for Contents of Named Range

excel_ga

New Member
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>
 

jtakw

Well-known Member
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
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
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

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top