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
=IF(ISNUMBER(SEARCH("A9270
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"))ASKER
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,"*A927
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,"*A927
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes same statement as fluoride, even if dental, oral or teeth are found.
Then the suggested formula would work for you.
ASKER
Neeraj,
Worked perfectly.. Thank you very much. can you tell me what the * means or does?
,{"*Fluoride*","*Dental*",
Worked perfectly.. Thank you very much. can you tell me what the * means or does?
,{"*Fluoride*","*Dental*",
* 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.
But if your cells contain only Fluoride, you may remove the * from the formula.
You're welcome. Glad to help.
ASKER
Thank you for explaining... makes sense now. take care
You're welcome.
Thanks and you too.
Thanks and you too.
but i suggest you use excel tables instead of whole column reference in AVto AZ
here is the formula
=IF(ISNUMBER(SEARCH(AV:AZ,
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.