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
[TABLE="width: 137"]
<colgroup><col></colgroup><tbody>[TR]
[TD]C.E.O.[/TD]
[/TR]
[TR]
[TD]C.F.O.[/TD]
[/TR]
[TR]
[TD]CCO[/TD]
[/TR]
[TR]
[TD]CEO[/TD]
[/TR]
[TR]
[TD]CF)[/TD]
[/TR]
[TR]
[TD]CFO[/TD]
[/TR]
[TR]
[TD]Chief Bottle Washer[/TD]
[/TR]
[TR]
[TD]COO[/TD]
[/TR]
[TR]
[TD]EPO[/TD]
[/TR]
[TR]
[TD]EVP[/TD]
[/TR]
[TR]
[TD]Executive Director[/TD]
[/TR]
[TR]
[TD]Executive Manager[/TD]
[/TR]
[TR]
[TD]Executive Sales[/TD]
[/TR]
[TR]
[TD]Founder[/TD]
[/TR]
[TR]
[TD]Genearl Manager[/TD]
[/TR]
[TR]
[TD]General Manager[/TD]
[/TR]
[TR]
[TD]GM[/TD]
[/TR]
[TR]
[TD]Managing Director[/TD]
[/TR]
[TR]
[TD]Managing Partner[/TD]
[/TR]
[TR]
[TD]Officer[/TD]
[/TR]
[TR]
[TD]Owner[/TD]
[/TR]
[TR]
[TD]Partner[/TD]
[/TR]
[TR]
[TD]President[/TD]
[/TR]
[TR]
[TD]Prez.[/TD]
[/TR]
[TR]
[TD]Principal [/TD]
[/TR]
[TR]
[TD]V.P.[/TD]
[/TR]
[TR]
[TD]V/P[/TD]
[/TR]
[TR]
[TD]VP[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
 
Upvote 0
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.
 
Upvote 0
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&" ")))
 
Upvote 0

Similar threads

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top