Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
William
 
Posts: n/a
Default Vertical Lookup problem

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR
 
Posts: n/a
Default Vertical Lookup problem

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




....."William" wrote in message
...
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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
L. Howard Kittle
 
Posts: n/a
Default Vertical Lookup problem

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



"William" wrote in message
...
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



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
James
 
Posts: n/a
Default Vertical Lookup problem

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

"William" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Range Lookup - Problem Jose M via OfficeKB.com Excel Worksheet Functions 0 August 9th 05 04:00 AM
Range Lookup - Problem Jose M via OfficeKB.com Excel Worksheet Functions 0 August 9th 05 03:59 AM
Lookup Problem Scott Excel Worksheet Functions 0 April 18th 05 08:07 PM
Lookup Problem Annette Excel Worksheet Functions 1 April 16th 05 03:10 AM
Vertical lookup help needed Have three columns R,S,T Excel Worksheet Functions 1 December 11th 04 12:11 PM


All times are GMT +1. The time now is 12:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"