Matching data

[Lady Success]

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$3737)<=D$36,INDEX(DrgList,SMALL(IF(ISNUMBER(SEARCH(DRUGENTRY,DrgList)),ROW(DrgList)),ROWS(D$3737))-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(DrgList,SMALL(IF(ISNUMBER(SEARCH(D37,DrgList)),ROW(DrgList)),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?

 

[Domenic]

Can you provide a small sample of the data, along with the actual
expected results?

 

[Lady]

The database contents would look something like this:
ColA ColB ColC
Drug Name Chemical Name HCPCS
Copegus ribavirin J8499
Rebetol ribavirin No JCode
Ribapak ribivarin J0880
Ribaphere ribivarin j0880
Gamastan gamma glob J1460
Gamastan gamma glob J1470
Gamastan gamma glob J1500

If Query sheet indicates Chemical Name: "Ribavirin", I want two things to
happen in my "qualifications" sheet. I want to list the name of the drug
associated with ribavirin and their corresponding HCPCS code (i.e. Copegus
(Col A) and J8499 (Col B); Rebetol (Col A) and No Jcode (Col B), etc.

If the Query sheet indicates Brand Name: "Gamastan" I want the
qualifications sheet to indicate Gamastan (Col A1) and J1460 (Col B1),
Gamastan (Col A2) and J1470 (Col B2), etc

The Gamastan scenario works ok with the current formula, but the Ribavirin
does not. I'm sure there's a glitch in my formula, but I just haven't been
able to identify it.

Thanks for any help!
(I have a dickens of a problem accessing this post and replying to it. I
ended up creating a new login. Is there a problem?)

 

[Domenic]

For simplicity, let's assume that A2:C8 contains the data, and E2
contains the drug or chemical name of interest. Then try...

F2:

=IF(ISNUMBER(MATCH("*"&E2&"*",B2:B8,0)),COUNTIF(B2:B8,"*"&E2&"*
"),COUNTIF(A2:A8,"*"&E2&"*"))

G2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(G$2:G2)<=
$F$2,INDEX($A$2:$A$8,SMALL(IF(ISNUMBER(SEARCH($E$2,$A$2:$A$8)),ROW($A$2:$
A$8)-ROW($A$2)+1,IF(ISNUMBER(SEARCH($E$2,$B$2:$B$8)),ROW($A$2:$A$8)-
ROW($A$2)+1)),ROWS(G$2:G2))),"")

H2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(H$2:H2)<=
$F$2,INDEX($C$2:$C$8,SMALL(IF(ISNUMBER(SEARCH($E$2,$A$2:$A$8)),ROW($A$2:$
A$8)-ROW($A$2)+1,IF(ISNUMBER(SEARCH($E$2,$B$2:$B$8)),ROW($A$2:$A$8)-
ROW($A$2)+1)),ROWS(H$2:H2))),"")