Looking for a formula that locates specific text in range of cells are returns specific text value

[RDAVIS1976]

What I'm trying to build is a formula that will search a range of cells and return a specified text value if there's a match. Specifically I'm looking for names which may appear along a reports to hierarchy, 1-7. If the formula finds a particular name, any where in the range, I want it to return the name I specify.

For example, I have 3 managers named; Mountain Man, Dan Fountain, and Now Begin...what formula would I use to locate them based on the table below?

I was using - IF(ISNUMBER(SEARCH("Man, Mountain",H13:M13)),"Mountain Man",IF(ISNUMBER(SEARCH("Fountain, Dan",H13:M13)),"Dan Fountain",IF(ISNUMBER(SEARCH("Begin, Now",H13:M13)),"Now Begin"))) but it's not working
[TABLE="width: 972"]
<colgroup><col span="6"></colgroup><tbody>[TR]
[TD]Supervisor Level 1 Name

[/TD]
[TD]Supervisor Level 2 Name

[/TD]
[TD]Supervisor Level 3 Name

[/TD]
[TD]Supervisor Level 4 Name

[/TD]
[TD]Supervisor Level 5 Name

[/TD]
[TD]Supervisor Level 6 Name

[/TD]
[/TR]
[TR]
[TD]Man, Mountain[/TD]
[TD]Krieg, Dave[/TD]
[TD]Sweeny, Jim[/TD]
[TD]Wilson, Russ[/TD]
[TD]Baldwin, Doug[/TD]
[TD]Sweeny, Jim[/TD]
[/TR]
[TR]
[TD]Sweeny, Jim[/TD]
[TD]Dilfer, Trent[/TD]
[TD]Krieg, Dave[/TD]
[TD]Fountain, Dan[/TD]
[TD]Parcells, Bill[/TD]
[TD]Parcells, Bill[/TD]
[/TR]
[TR]
[TD]Parcells, Bill[/TD]
[TD]Begin, Now[/TD]
[TD]Parcells, Bill[/TD]
[TD]Krieg, Dave[/TD]
[TD]Sweeny, Jim[/TD]
[TD]Krieg, Dave[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

Use COUNTIF instead.
You can shorten (simplify) the formula by using Cell references for the Names...

Book1
	H	I	J	K	L	M	N
12							Results
13	Man, Mountain	Krieg, Dave	Sweeny, Jim	Wilson, Russ	Baldwin, Doug	Sweeny, Jim	Mountain Man
14	Sweeny, Jim	Dilfer, Trent	Krieg, Dave	Fountain, Dan	Parcells, Bill	Parcells, Bill	Dan Fountain
15	Parcells, Bill	Begin, Now	Parcells, Bill	Krieg, Dave	Sweeny, Jim	Krieg, Dave	Now Begin
Sheet411

Cell Formulas
Range	Formula
N13	=IF(COUNTIF(H13:M13,"Man, Mountain"),"Mountain Man",IF(COUNTIF(H13:M13,"Fountain, Dan"),"Dan Fountain",IF(COUNTIF(H13:M13,"Begin, Now"),"Now Begin","No match")))

 

[RDAVIS1976]

Thank you!

 

[jtakw]

You're welcome, welcome to the forum.