Multiple Lookups - SIngle Result
 

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

Multiple Lookups - SIngle Result
 

One way

Assume source table in cols A to C, data from row2 to say, row1001

Suppose you have the cities and states combinations to be looked up running
in E2 and F2 down

You could put in G2, then array-enter the formula by pressing
CTRL+SHIFT+ENTER (instead of just pressing ENTER):
=INDEX(C$2:C$1000,MATCH(1,(A$2:A$1000=E2)*(B$2:B$1 000=F2),0))
Copy G2 down to return the required results.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"REBrown" wrote:
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

Multiple Lookups - SIngle Result
 

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

Multiple Lookups - SIngle Result
 

Typo/gremlin correction ..

Line:
Assume source table in cols A to C, data from row2 to say, row1001

should have read:
Assume source table in cols A to C, data from row2 to say, row1000

Note: Adapt the source ranges to suit within the INDEX/MATCH
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Multiple Lookups - SIngle Result
 

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

Multiple Lookups - SIngle Result
 

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

Multiple Lookups - SIngle Result
 

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

Multiple Lookups - SIngle Result
 

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

Multiple Lookups - SIngle Result
 

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