# 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.

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?

 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>

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### 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.

But, why not just use COUNTIF:

#### 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.

But, why not just use COUNTIF:

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:

OR