"Search" Function with answer prepared by Biff for Brantty on 01/0

Dear All,

I found the following beautiful formula from one question raised by Mr
Brantty on 01/08/2005 and the answer is prepared by Mr Biff.

=IF(OR(ISNUMBER(SEARCH({"USDA","GRADE"},C1))),1,"")

I would like to apply the search by data field instead of specified data
like "USDA". My search data involve more than 2 criteria and they change
every time that it would be very troublesome to modify the formula every time.

I tried =IF(OR(ISNUMBER(SEARCH({$A$1,$A$2},C1))),1,"") , but it does not work.

In my case I prefer not to use vlookup or VBA or macro in order to ease the
users.

Thanks

Frances

Try...

=IF(OR(ISNUMBER(SEARCH($A$1:$A$2,C1))),1,"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article <E7361CE6-1714-4131-930B-199A4B4618FB@microsoft.com>,
"Frances" <Frances@discussions.microsoft.com> wrote:

> Dear All,
>
> I found the following beautiful formula from one question raised by Mr
> Brantty on 01/08/2005 and the answer is prepared by Mr Biff.
>
> =IF(OR(ISNUMBER(SEARCH({"USDA","GRADE"},C1))),1,"")
>
> I would like to apply the search by data field instead of specified data
> like "USDA". My search data involve more than 2 criteria and they change
> every time that it would be very troublesome to modify the formula every time.
>
> I tried =IF(OR(ISNUMBER(SEARCH({$A$1,$A$2},C1))),1,"") , but it does not work.
>
> In my case I prefer not to use vlookup or VBA or macro in order to ease the
> users.
>
> Thanks
>
> Frances

Domenic:

Thank you very very for your help.

Frances

"Domenic" wrote:

> Try...
>
> =IF(OR(ISNUMBER(SEARCH($A$1:$A$2,C1))),1,"")
>
> ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
>
> Hope this helps!
>
> In article <E7361CE6-1714-4131-930B-199A4B4618FB@microsoft.com>,
> "Frances" <Frances@discussions.microsoft.com> wrote:
>
> > Dear All,
> >
> > I found the following beautiful formula from one question raised by Mr
> > Brantty on 01/08/2005 and the answer is prepared by Mr Biff.
> >
> > =IF(OR(ISNUMBER(SEARCH({"USDA","GRADE"},C1))),1,"")
> >
> > I would like to apply the search by data field instead of specified data
> > like "USDA". My search data involve more than 2 criteria and they change
> > every time that it would be very troublesome to modify the formula every time.
> >
> > I tried =IF(OR(ISNUMBER(SEARCH({$A$1,$A$2},C1))),1,"") , but it does not work.
> >
> > In my case I prefer not to use vlookup or VBA or macro in order to ease the
> > users.
> >
> > Thanks
> >
> > Frances
>

Dear Domenic,

Thanks very much for your help with the formula below.

How can I apply this formula to the conditional formatting as I want to
highlight the data found?

Frances

> "Domenic" wrote:
>
> > Try...
> >
> > =IF(OR(ISNUMBER(SEARCH($A$1:$A$2,C1))),1,"")
> >
> > ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
> >
> > Hope this helps!
> >
> > In article <E7361CE6-1714-4131-930B-199A4B4618FB@microsoft.com>,
> > "Frances" <Frances@discussions.microsoft.com> wrote:
> >
> > > Dear All,
> > >
> > > I found the following beautiful formula from one question raised by Mr
> > > Brantty on 01/08/2005 and the answer is prepared by Mr Biff.
> > >
> > > =IF(OR(ISNUMBER(SEARCH({"USDA","GRADE"},C1))),1,"")
> > >
> > > I would like to apply the search by data field instead of specified data
> > > like "USDA". My search data involve more than 2 criteria and they change
> > > every time that it would be very troublesome to modify the formula every time.
> > >
> > > I tried =IF(OR(ISNUMBER(SEARCH({$A$1,$A$2},C1))),1,"") , but it does not work.
> > >
> > > In my case I prefer not to use vlookup or VBA or macro in order to ease the
> > > users.
> > >
> > > Thanks
> > >
> > > Frances
> >