I have a sheet that contains a sheet where the user enters
data(Sheet1=Query), another where it gathers information
(Sheet2=Qualifications), and a sheet that contains a database
(Sheet3=Database).
ColA: Drug Name
ColB: Chemical Name
ColC: HCPCS

The DRUGENTRY cell in the Query sheet can pull the name of the drug from the
Drug Name column or the Chemical name.

In Sheet 2, I have cell D36 counting the number of instances the name of the
drug is entered into Sheet1. The formula used in that cell is:
=IF(DRUGENTRY="","",COUNTIF(DrgList,"*"&DRUGENTRY& "*"))

In Cell D37 €“ D51, I used the following array formula to identify the name
of the drugs identified in the number counted in D36.
{=IF(D$36="","",IF(ROWS(D$37:D37)<=D$36,INDEX(DrgL ist,SMALL(IF(ISNUMBER(SEARCH(DRUGENTRY,DrgList)),R OW(DrgList)),ROWS(D$37:D37))-MIN(ROW(DrgList))+1,1),""))}

In Cell E37, I used the following array formula to identify the HCPCS code
associated with the drug.
{=IF(D$36="","",IF(ROWS(E$37:E37)<=D$36,INDEX(DrgL ist,SMALL(IF(ISNUMBER(SEARCH(D37,DrgList)),ROW(Drg List)),ROWS(E$37:E37))-MIN(ROW(DrgList))+1,3),""))}

My problem is, if D36 counts the same drug (i.e. Gamastan, - listed in the
DrugName Col) multiple times, D37-D50 lists Gamastan multiple times €“ which
is fine. E36-50 is then able to identify the corresponding HCPCS codes.
However, if I change the DRUGENTRY to €œribavirin€� which is listed in the
€œChemical Name€� column in the database, D37;D39 shows the drug names of the
drugs associated with the chemical name, ribavirin (i.e. Copegus, Rebetol,
Ribapak) €“ which is what I want. But E36 indicates the HCPCS associated with
Copegus, but then indicates #NUM! error on the remaining number of drugs.
What am I doing wrong in my formula?



--
Lady