I have a similiar problem. Can you at the 2 posting by me in the last couple
of days. Yesterday and today. Here is the link to both posting:

Firts posting:

http://www.microsoft.com/office/comm...3-300cf7e0af2a


2nd posting:


http://www.microsoft.com/office/comm...a-8bf12f284981
"T. Valko" wrote:

You're welcome. Thanks for the feedback!

Biff

"REBrown" wrote in message
...
Thanks so much - I would never have figured that one out!!!
--
REBrown


"T. Valko" wrote:

Here's a fast non-array method but it does come with a caveat.

Assume:

A1:A19275 = city
B1:B19275 = state
C1:C19275 = county

Table is sorted by state

E1 = city lookup
F1 = state lookup

=INDEX(C19275:INDEX(C:C,MATCH(F1,B:B,0)),MATCH(E1, A19275:INDEX(A:A,MATCH(F1,B:B,0)),0))

The caveat: if your lookup city does not exist in the lookup state but
does
exist in another state you might get an incorrect result.

Biff

"REBrown" wrote in message
...
T Valko...thanks for the response...I can sort the Ref_Cities! table by
state
as it is used only to look up the count...how would the array query
look?
--
REBrown


"T. Valko" wrote:

This could be done with an array formula but might be kind of slow
since
you
have almost 20K rows of data. If your table was sorted by state you
could
use a more efficient non-array formula. Any chance this is the case?

Biff

"REBrown" wrote in message
...
I have a multi-tab spreadsheet with one of the forms a simple
customer,
address, city, state, Zip. I want to use the combination of city
and
state
to lookup the correct county in another table. The problem is that
there
can
be many to many match on the cities so I always get the first city
in
lthe
list. Here's my formula:

=VLOOKUP(C4,Ref_Cities!A2:C19275,3,FALSE)

The cities table looks like this:

A B C
City State County
Abbeville Alabama Henry
Abbeville Georgia Wilcox
Abbeville Louisiana Vermilion
Abbeville Mississippi Lafayette

Any ideas on how to get around the multiple cities problem?
THANKS!!!!

--
REBrown