I initially used the formula on a subset of test data and it worked great.
However, when I applied it to the entire database, I found that there were
entries in the database that did not fall sequentially because of the
wording.

The database looks like this:
Drug Name Coverage Status
ACETIC ACID/HYDROCORTISONE Covered Generic
ACETYLCYSTEINE Covered Generic
ACIDIC VAGINAL Covered Generic
ACNE MEDICATION Covered Generic
ACTICIN Covered Generic
ACYCLOVIR Covered Generic
ADRENALIN CHLORIDE Covered Generic
ADVANCED NATALCARE Covered Generic
HYDROCORTISONE Covered Generic
HYDROCORTISONE ACETATE Covered Generic
HYDROCORTISONE BUTYRATE Covered Generic
HYDROCORTISONE VALERATE Covered Generic
HYDROGESIC Covered Generic
HYDROMORPHONE HCL Covered Generic

Searching for Hydrocotisone, it found 5 entries (which is correct).
However, because ACETIC ACID/HYDROCORTISONE is found first in the list, it
pulls up the five drugs following that and as a result does not identify all
the others correctly. I know you said the content had to be together (and in
most cases it is), but there may be cases such as this where it is not. Do
you have any ideas on how I can identify the correct five?

--
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