Excel formula - find with multiple ORs 2

[flashbbeb]

Hi all,

I'm trying to populate a column that shows whether someone is a supervisory employee. The formula should parse a parallel cell in the job title column for any of the following strings:

"AVP","Manager","Mgr","Spvr","Supervisor","Supv","SVP","V P","Vice Pres","Vice President","VP"

If ANY of these is found, then the value should be "Yes".

The most logical thing I could think of with my somewhat limited formula knowledge was this (where "G2" is a cell in the job title column):

=IF(FIND(OR("AVP","Manager","Mgr","Spvr","Supervisor","Supv","SVP","V P","Vice Pres","Vice President","VP"),G2,1),"Yes","No")

Even for TRUE cases, I didn't see a Yes or No, just sad old #VALUE!

Any ideas?

Thanks,
EB

 

[SkipVought]

Hi,

Put your values in a LIST. I use Named Ranges -- faq68-1331.
[tt]
=if(ISNA(Match(G2,YourListName,0)),"No","Yes")
[/tt]
or you could use your list range reference instead of a Range Name.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[flashbbeb]

Thanks, Skip. This solves my OR issue!

The only problem left, though, is that the Job Title column doesn't have an exact match - all of the management designations are pro- or preceded by something else. That's why I added FIND to parse the strings.

How would I add that in there to work? With the range, it's checking that job title column for an exact match that won't exist because of the additional aspects of the titles.

Thanks,
EB

 

[SkipVought]

How many different values do you have, that "quallify"?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Too many for a nested IF!

Personally would do this with a UDF (VBA) but if that's not your bag then you may be able to do something like:

=IF(NOT(ISERROR(FIND("AVP",G2))),"Y","") & IF(NOT(ISERROR(FIND("Manager,"Y","") & =IF(NOT(ISERROR(FIND("Mgr2))),"Y","")...etc etc

Will be a bit of a beast though

For a UDF, you could use something like

Function IsSupervisor(strJob As String)
Dim c As Range
Dim Result

IsSupervisor = "N"

For Each c In Sheets("Lookups").Range("SupervisorTitles")
  Result = InStr(UCase(strJob), UCase(c))
  If Result > 0 Then
    'string found
    IsSupervisor = "Y"
    Exit For
  End If
Next
End Function

This assumes a sheet called lookups and a named range for all your supervisor strings called "SupervisorTitles"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[lionelhill]

I would be sorely tempted to ask for permission to tidy up the column of job titles before going further. Even if it's huge, it wouldn't be a long job to fill out any Spvrs into supervisors, and the longer you keep a column where anyone can type whatever they want, the more likely it is that people will invent new abbreviations that you'll have to add to your list. Not to mention the risk that someone will enter "visiting personnel" as "VP" and create an awful lot of vice presidents, or similar confusion.

 

[flashbbeb]

That UDF did the trick - thanks Geoff (xlbo)!!