We help IT Professionals succeed at work.

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

Culwatrnca11
Culwatrnca11 asked
on
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"))
Comment
Watch Question

Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
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.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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

Culwatrnca11Data Analyst

Author

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"))
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
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

Culwatrnca11Data Analyst

Author

Commented:
yes same statement as fluoride, even if dental, oral or teeth are found.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Then the suggested formula would work for you.
Culwatrnca11Data Analyst

Author

Commented:
Neeraj,

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

,{"*Fluoride*","*Dental*","*Oral*","*Teeth*"}))>
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
* 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.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome. Glad to help.
Culwatrnca11Data Analyst

Author

Commented:
Thank you for explaining... makes sense now. take care
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome.
Thanks and you too.