Biff,

This worked very well. Thank You! Would it be possible for you to step
through the row and index functions so I can understand better what they are
doing? I stepped it through the wizard and have a little better
understanding, but not sure that I totally understand how the functions work.
In particular, the INDEX(Sheet2!A:A) function. If just the column is
entered without indicating a row, does that allow the function to access the
entire database? I'm not sure exactly what the INDEX does. Can you explain
it a little better than the "Help"? I'm assuming that MATCH("*"&B$2&"*") is
identifying the entire string in a cell by using a wildcard?

Everytime I use a suggestion I get in this forum, I've been able to use or
expand it in future worksheets. It's been a very useful tool for me to
expand my knowledge of Excel. Thanks for all of the help you've been!


--
Lady


"T. Valko" wrote:

One way:

Assuming your DB is sorted or grouped so that all instances of the lookup
drug are grouped together.

Use a cell that returns the number of records found. Maybe use cell C1 and
in cell B1 you could enter: Records Found.

Enter this formula in C1:

=IF(B2="","",COUNTIF(Sheet2!A:A,"*"&B2&"*"))

Enter this formula in C2:

=IF(B$2="","",IF(ROWS(C$2:C2)<=C$1,INDEX(Sheet2!A: A,MATCH("*"&B$2&"*",Sheet2!A:A,0)+ROWS(C$2:C2)-1),""))

Enter this formula in D2:

=IF(C2="","",INDEX(Sheet2!B:B,MATCH(C2,Sheet2!A:A, 0)))

Select both C2 and D2 and copy down to C6:D6 or to a number of cells that is
at least equal to the max number of instances any drug that may appear in
your DB.

--
Biff
Microsoft Excel MVP


"Lady Success" wrote in message
...
I am creating a query worksheet where text is entered. I then want to
verify
the status of that entry against a database. I need to identify anything
that contains all or part of the name listed in the query. It would look
something like this:

Sheet 1 is where the query is:

A1 "Type in drug name"
B2 Drug name is entered by user
C2 - through C6 I want the formula(s) to find the drug name shown
in
B2 and if more than one instance, indicate all the instances it found.
D2 through D6 - I want the formula(s) to find the content of Column B in
the
database that matches the content of C2- through C6.

The example and outcome would look like this:

The word "Nifedipine" is entered in the query sheet.

I want the query formulas to come back with the following responses:

C2 Nifedipine D2 Covered generic
C3 Nifedipine ER D3 Non-Covered Generic


Sheet 2 contains the database:

Column A Column B
NIFEDICAL XL Covered Generic
NIFEDIPINE Covered Generic
NIFEDIPINE ER Non-Covered Generic
NILSTAT Covered Generic
NIMODIPINE Covered Generic
NISOLDIPINE Covered Generic
NITREK Covered Generic
NITRO-BID Covered Generic
NITROFURANTOIN Covered Generic

Is it even possible for me to do what I want to do? I know how to use the
Vlookup function, but not sure how I can identify an entire string rather
than just the exact match and also identify more than one instance of the
name?


--
Lady