I am having difficulty using a look-up function. Here is the formula I'm
using:

=LOOKUP('Nurse Finders'!A2,Lookups!E$3:E$35,Lookups!F$3:F$35)

I've used this formula many times without problems, however in this
particular application it is not returning the correct results. I think the
problem is this. In the first set of lookups (E$3:E$35), a partial list is
as follows:

Gilbert Hospital
KP - Antioch
KP - Bellflower
KP - Fontana - ED
KP - Santa Clara
KP - Walnut Creek
Northern Arizona

The second lookup (F) has the corresponding city for each of the facilities
listed in the first lookup (E)

On the page where the interepreted results are displayed, it is not
accurately listing the cities. Is this because the first lookup (E) has
facilities that are all named "KP - *" and it can't sort through this
properly? If so, is there a work around?

Just as an FYI, the original data is imported from an association and then
being formatted to import to our data base so the best possible solution is
one that does not involve manually changing the names of the facility as we
import on a weekly basis.

Thanks for any help you can offer.

As long as the lookup_vector is sorted in ascending order and the
lookup_value is an "exact" match to one of the items in the lookup_vector it
should return the correct result. Using the small sample you posted works
just fine for me.

You might not have an "exact" match due to unseen characters like
leading/trailing spaces in either (or both) the lookup_value or the
lookup_vector.

--
Biff
Microsoft Excel MVP


"Pat" wrote in message
...
I am having difficulty using a look-up function. Here is the formula I'm
using:

=LOOKUP('Nurse Finders'!A2,Lookups!E$3:E$35,Lookups!F$3:F$35)

I've used this formula many times without problems, however in this
particular application it is not returning the correct results. I think
the
problem is this. In the first set of lookups (E$3:E$35), a partial list
is
as follows:

Gilbert Hospital
KP - Antioch
KP - Bellflower
KP - Fontana - ED
KP - Santa Clara
KP - Walnut Creek
Northern Arizona

The second lookup (F) has the corresponding city for each of the
facilities
listed in the first lookup (E)

On the page where the interepreted results are displayed, it is not
accurately listing the cities. Is this because the first lookup (E) has
facilities that are all named "KP - *" and it can't sort through this
properly? If so, is there a work around?

Just as an FYI, the original data is imported from an association and then
being formatted to import to our data base so the best possible solution
is
one that does not involve manually changing the names of the facility as
we
import on a weekly basis.

Thanks for any help you can offer.

Biff, thank you!! That was the exact problem, I had trailing spaces in every
single one of the lookup_vectors.

Pat

"T. Valko" wrote:

As long as the lookup_vector is sorted in ascending order and the
lookup_value is an "exact" match to one of the items in the lookup_vector it
should return the correct result. Using the small sample you posted works
just fine for me.

You might not have an "exact" match due to unseen characters like
leading/trailing spaces in either (or both) the lookup_value or the
lookup_vector.

--
Biff
Microsoft Excel MVP


"Pat" wrote in message
...
I am having difficulty using a look-up function. Here is the formula I'm
using:

=LOOKUP('Nurse Finders'!A2,Lookups!E$3:E$35,Lookups!F$3:F$35)

I've used this formula many times without problems, however in this
particular application it is not returning the correct results. I think
the
problem is this. In the first set of lookups (E$3:E$35), a partial list
is
as follows:

Gilbert Hospital
KP - Antioch
KP - Bellflower
KP - Fontana - ED
KP - Santa Clara
KP - Walnut Creek
Northern Arizona

The second lookup (F) has the corresponding city for each of the
facilities
listed in the first lookup (E)

On the page where the interepreted results are displayed, it is not
accurately listing the cities. Is this because the first lookup (E) has
facilities that are all named "KP - *" and it can't sort through this
properly? If so, is there a work around?

Just as an FYI, the original data is imported from an association and then
being formatted to import to our data base so the best possible solution
is
one that does not involve manually changing the names of the facility as
we
import on a weekly basis.

Thanks for any help you can offer.

That's a common problem especially when the data is imported or generated
from another aplication. Glad you found the problem.

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Pat" wrote in message
...
Biff, thank you!! That was the exact problem, I had trailing spaces in
every
single one of the lookup_vectors.

Pat

"T. Valko" wrote:

As long as the lookup_vector is sorted in ascending order and the
lookup_value is an "exact" match to one of the items in the lookup_vector
it
should return the correct result. Using the small sample you posted works
just fine for me.

You might not have an "exact" match due to unseen characters like
leading/trailing spaces in either (or both) the lookup_value or the
lookup_vector.

--
Biff
Microsoft Excel MVP


"Pat" wrote in message
...
I am having difficulty using a look-up function. Here is the formula
I'm
using:

=LOOKUP('Nurse Finders'!A2,Lookups!E$3:E$35,Lookups!F$3:F$35)

I've used this formula many times without problems, however in this
particular application it is not returning the correct results. I
think
the
problem is this. In the first set of lookups (E$3:E$35), a partial
list
is
as follows:

Gilbert Hospital
KP - Antioch
KP - Bellflower
KP - Fontana - ED
KP - Santa Clara
KP - Walnut Creek
Northern Arizona

The second lookup (F) has the corresponding city for each of the
facilities
listed in the first lookup (E)

On the page where the interepreted results are displayed, it is not
accurately listing the cities. Is this because the first lookup (E)
has
facilities that are all named "KP - *" and it can't sort through this
properly? If so, is there a work around?

Just as an FYI, the original data is imported from an association and
then
being formatted to import to our data base so the best possible
solution
is
one that does not involve manually changing the names of the facility
as
we
import on a weekly basis.

Thanks for any help you can offer.