Look-Up Function

[Pat]

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.

 

[T. Valko]

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.

 

[Pat]

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

Pat

 

[T. Valko]

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!