I have a work book with 2 sheets in.

One has a listing of drugs.

Drug
Abacavir Sulfate
Abelcet
Abilify
Aciphex
Actinex

Other sheet has a database listing with a few columns as follows:

Description med rec Legend Date

ACCUZYME OINTMENT 6237 Y 2-Jun-07
ACCUZYME OINTMENT 6237 Y 24-Jun-07
ACETAMINOPHEN 325 6180 N 16-Jun-07
ACETAZOLAMIDE 250 6247 Y 15-Jun-07
ACETAZOLAMIDE 250 6247 Y 15-Jun-07
ACETAZOLAMIDE 250 6247 Y 20-Jun-07
ACULAR 0.5% EYE D 22216 Y 2-Jun-07
ADOXA PAK 1/150 M 6253 Y 20-Jun-07

What I'm trying to do is check to see if a drug from sheet one is included
in any of the test listing on shee 2 under description. The description also
includes other description besides the drug name. I would then like to
return the med rec# if the drug is found. Any heklp would be greatly
appreciated.

Not an easy one !
=SUM(ROW()*IF(ISERROR(FIND(I19,E15:E22)),0,1))
where E15:E22 is the range that I used to lookup - so in your case the
second sheet column A and I19 is the place where to find the name you are
looking for.
Note that this MUST be entered as an ARRAY formula (<Ctrl+<Shift+<Enter)
and can then be copied downwards.
HEALTH WARNING: The formula gives the sum of all rownumbers where this can
be found - ie if it is found more then once the answer is incorrect
=SUM(IF(ISERROR(FIND(I19,E15:E22)),0,1)) will give you the amount of time
the name is found. Sorry - could not split this out, even though I know that
it is possible ! Search in the database for "Ingrid Array" and you might find
a couple of hints.

HEALTH WARNING2: the formula only works when the Capitalisation of the name
is the same in the search as in the lookup. Ie Acular will NOT find ACULAR.


RDWJ

"davidio" wrote:

I have a work book with 2 sheets in.

One has a listing of drugs.

Drug
Abacavir Sulfate
Abelcet
Abilify
Aciphex
Actinex

Other sheet has a database listing with a few columns as follows:

Description med rec Legend Date

ACCUZYME OINTMENT 6237 Y 2-Jun-07
ACCUZYME OINTMENT 6237 Y 24-Jun-07
ACETAMINOPHEN 325 6180 N 16-Jun-07
ACETAZOLAMIDE 250 6247 Y 15-Jun-07
ACETAZOLAMIDE 250 6247 Y 15-Jun-07
ACETAZOLAMIDE 250 6247 Y 20-Jun-07
ACULAR 0.5% EYE D 22216 Y 2-Jun-07
ADOXA PAK 1/150 M 6253 Y 20-Jun-07

What I'm trying to do is check to see if a drug from sheet one is included
in any of the test listing on shee 2 under description. The description also
includes other description besides the drug name. I would then like to
return the med rec# if the drug is found. Any heklp would be greatly
appreciated.