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