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

RDAVIS1976

New Member
Joined
Dec 28, 2016
Messages
2
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 :(
Supervisor Level 1 Name

Supervisor Level 2 Name

Supervisor Level 3 Name

Supervisor Level 4 Name

Supervisor Level 5 Name

Supervisor Level 6 Name

Man, MountainKrieg, DaveSweeny, JimWilson, RussBaldwin, DougSweeny, Jim
Sweeny, JimDilfer, TrentKrieg, DaveFountain, DanParcells, BillParcells, Bill
Parcells, BillBegin, NowParcells, BillKrieg, DaveSweeny, JimKrieg, Dave

<colgroup><col span="6"></colgroup><tbody>
</tbody>
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi,

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


Book1
HIJKLMN
12Results
13Man, MountainKrieg, DaveSweeny, JimWilson, RussBaldwin, DougSweeny, JimMountain Man
14Sweeny, JimDilfer, TrentKrieg, DaveFountain, DanParcells, BillParcells, BillDan Fountain
15Parcells, BillBegin, NowParcells, BillKrieg, DaveSweeny, JimKrieg, DaveNow Begin
Sheet411
Cell Formulas
RangeFormula
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")))
 
Upvote 0
You're welcome, welcome to the forum.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

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