If search finds a certain word in range then add specific comment to different cell

How do I modify this so that it searches a range not single cells? So for example instead of AV2, I would like the range of AV:AZ to be searched for the word "A9270". Same for Fluoride.  Is it also possible to use Floride, Oral, Dental, Tooth. Any one of these could appear in a range. Then what would happen is the comments in the "" if match word is found will be added in a different column.

=IF(ISNUMBER(SEARCH("A9270",$AV2)),"A9270 By definition this is a HCPCS for non-covered services. Need instruction on benefit response.  Authorization has this code.", IF(ISNUMBER(SEARCH("Fluoride",$AV2))," Analysis required. Claims with Dental codes.","Analysis Required"))
Culwatrnca11Data AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
try this formula. it must be entered with control shift enter. becuase it is an array formula

but i suggest you use excel tables instead of whole column reference in AVto AZ

here is the formula

=IF(ISNUMBER(SEARCH(AV:AZ,{"A9270","Floride","Oral","Dental","Tooth"})),"Analysis Required","not required")

if any of those texts found in that AV:AZ then it will return analysis required and if not found then it will return not required.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this......

=IF(COUNTIF($AV:$AZ,"*A9270*"),"A9270 By definition this is a HCPCS for non-covered services. Need instruction on benefit response.  Authorization has this code.", IF(COUNTIF($AV:$AZ,"*Fluoride*")," Analysis required. Claims with Dental codes.","Analysis Required"))

Open in new window

0
Culwatrnca11Data AnalystAuthor Commented:
Neeraj,

Your version is working for my situation, how would you modify it to include multiple criteria? Fluoride, Dental, Oral, Teeth. Any one of these could appear in the range.

=IF(COUNTIF($AV:$AZ,"*A9270*"),"A9270 By definition this is a HCPCS for non-covered services. Need instruction on benefit response.  Authorization has this code.", IF(COUNTIF($AV:$AZ,"*Fluoride*")," Analysis required. Claims with Dental codes.","Analysis Required"))
0
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Do you want to return the same statement if any cell in the range AV:AZ contains a string with either Fluoride or Dental or Oral or Teeth?
If so, you may try this.....

=IF(COUNTIF($AV:$AZ,"*A9270*"),"A9270 By definition this is a HCPCS for non-covered services. Need instruction on benefit response.  Authorization has this code.", IF(SUM(COUNTIF($AV:$AZ,{"*Fluoride*","*Dental*","*Oral*","*Teeth*"}))>0," Analysis required. Claims with Dental codes.","Analysis Required"))

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Culwatrnca11Data AnalystAuthor Commented:
yes same statement as fluoride, even if dental, oral or teeth are found.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Then the suggested formula would work for you.
0
Culwatrnca11Data AnalystAuthor Commented:
Neeraj,

Worked perfectly..  Thank you very much.  can you tell me what the * means or does?

,{"*Fluoride*","*Dental*","*Oral*","*Teeth*"}))>
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
* is a wildcard character. e.g. in *Fluoride* means there maybe some characters before Fluoride and after Fluoride.
But if your cells contain only Fluoride, you may remove the * from the formula.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to help.
0
Culwatrnca11Data AnalystAuthor Commented:
Thank you for explaining... makes sense now. take care
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome.
Thanks and you too.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.