Help with lookup (vlookup)

[davidio]

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.

 

[Harlan Grove]

I have a work book with 2 sheets in.

One has a listing of drugs.

Drug
Abacavir Sulfate
Abelcet ....
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 ....
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. . . .

Looks like there could be several entries in the second table for the
same drug. Lookup formulas would return just one of the matching med
rec values. You might want to add another field showing the number of
matching records.

More troublesome would be if the entries in the first table may not
match the spelling of the drug in the second table. I'll assume that's
not the case, and the only problem you face is that the drugs listed
in the first table would be substrings of entries in the Description
field in the second table. I'll also add a count column to the first
table along with the med rec of the first match.

If the first table were in A2:A100,

B2 [count of matches in second table]:
=COUNTIF(INDEX(SecondTable,0,1),"*"&A2&"*")

C2 [first matching med rec, if any]:
=IF(B2>0,VLOOKUP("*"&A2&"*",SecondTable,2,0),"")

Fill B2:C2 down into B3:C100.