Multiple Lookups - SIngle Result

[REBrown]

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!!!!

 

[Max]

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$1000=F2),0))
Copy G2 down to return the required results.

 

[T. Valko]

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

 

[Max]

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

 

[REBrown]

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?

 

[T. Valko]

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]

Thanks so much - I would never have figured that one out!!!

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Thanks so much - I would never have figured that one out!!!

 

[klafert]

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/com...af2a&mid=36c54b3a-ad58-4264-a073-300cf7e0af2a


2nd posting:


http://www.microsoft.com/office/com...&p=1&tid=0e9782b7-4952-46be-b1da-8bf12f284981