You're welcome. Thanks for the feedback!

I've written some fairly extensive explanations of formulas and I've marked
some of them with an "identifier". If you're so inclined you can do a search
of Google Groups for the phrase: Biff exp 101.

Spend an hour a day reading posts in this newsgroup and you'll be surprised
at how much you can learn!

--
Biff
Microsoft Excel MVP


"Lady Success" wrote in message
...
Thanks! This worked great! No need to explain the previous formula. I
think as I'm begin to use more of the functions in a nested fashion, I'll
get
the hang of it. Thanks for the help!
--
Lady


"T. Valko" wrote:

you said the content had to be together

It doesn't have to be but when the data is sorted or grouped together it
makes it easier.

Ok, we need to tweak the formulas. This new "main" formula is more
calculation intensive and may be slower to calculate if your DB has
1000's
of rows of data.

Let's use defined named ranges in these new formulas.

Assume your DB on sheet2 is in the range A2:B500. The drug name is in
column
A and the status is in column B.

Create these named ranges:

Goto the menu InsertNameDefine
Name: Drug
Refers to: =Sheet2!$A$2:$A$500

Name: Status
Refers to: =Sheet2!$B$2:$B$500

Formula in C1 to get the count of records:

=IF(B2="","",COUNTIF(Drug,"*"&B2&"*"))

The new formula in C2 is now an array formula** :

=IF(ROWS(C$2:C2)<=C$1,INDEX(Drug,SMALL(IF(ISNUMBER (SEARCH(B$2,Drug)),ROW(Drug)),ROWS(C$2:C2))-MIN(ROW(Drug))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Note that if you're *not* using Excel 2007 array formulas *can't* use
entire
columns as range references. That's why I've defined the named ranges
Drug
and Status to be specific sized ranges.

Formula in D2:

=IF(C2="","",INDEX(Status,MATCH(C2,Drug,0)))

Select both C2 and D2 and copy down as needed.

Since we changed the "main" formula I guess I no longer need to explain
how
the original formula worked?


--
Biff
Microsoft Excel MVP


"Lady Success" wrote in message
...
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