Vertical Lookup problem

[William]

My spreadsheet has three columns: City, State, Miles. I would like to know
how to create a lookup in which I can put in the city, such as Lexington, and
put in a state, such KY, and have it give me the results that is in column
three "miles." My spreadsheet looks similar to below:
City State Miles
Lexington NC 423
Nashville TN 501
Lexington KY 354
Hickory NC 645
Bristol TN 344
Lexington TN 233

 

[CLR]

I would use a CONCATINATION of the City and State, such as, Lexington_NC, or
Nashville_TN, in a column just to the left of your present City
column....and then do the looking up of your CONCATINATED data entry on
that column........

Vaya con Dios,
Chuck, CABGx3

 

[L. Howard Kittle]

Hi William,
Just found this today mucking about the groups. I believe it was by Bob
Phillips.

=INDEX(C2:C7,MATCH(E1&E2,A2:A7&B2:B7,0))

Array enter--CTRL+SHIFT+ENTER

Where the cities are in A2:A7
States are in B2:B7
Miles are in C2:C7
Enter city in E1
State in E2

 

[James]

Assuming City is keyed in into cell "A1"

City State Miles
Lexington NC 423
Nashville TN 501
Lexington KY 354
Hickory NC 645
Bristol TN 344
Lexington TN 233

City State
Lexington KY 354 => Answer Cell "C10"

Key in the following formula into Cell "C10"
=VLOOKUP(A10:B10,A2:C7,3) then enter Crtl+Shift+Enter