Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    vlookup help please (XP SP2)

    Good morning

    I gave a 1 sheet workbook in columns B36 to H200 I have rates and service information, there are certain calculations carried out through the use of 2 drop down boxes in B7 and D7. In column H I have transit times which I would now like to show when a country is selected from B7, I have tried the following - =VLOOKUP(B7,B35:H200,6,FALSE) which produces a #N/A error and I cannot figure out why, can somebody advise what they think I am doing wrong

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: vlookup help please (XP SP2)

    Steve,
    Could you provide an attachment?
    Thanks.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup help please (XP SP2)

    There are many possible reasons for the #NA error. It would be impossible to guess which is causing your problem without seeing the worksheet. Could you upload a workbook that shows the problem with any propritery data deleted or altered?
    Legare Coleman

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup help please (XP SP2)

    Thank you for both of your response

    I have attached the workbook and what I am trying to do is shown in C7, any pointers welcome

    Cheers

    Steve
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vlookup help please (XP SP2)

    Try

    =INDEX(H27:H191,B6)

    Explanation: the value of B6 is not the name of the selected city, but its index in the list. You can use this index to look up the corresponding value in column H.

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup help please (XP SP2)

    The result of your list box, in the eyes of Excel, is is the row number of the input range. For example "Austria - Vienna" = 1 and "Belgium - Brussels"= 4. You can use the OFFSET function to achieve what you want

    C7's formula would be: =OFFSET(B27,B6-1,6)

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup help please (XP SP2)

    Thanks Hans & Mbarren for your quick response

    Both worked very well so it leaves me with a final question, which one, and why would it be the better one to use, is there a downfall in using a particular one?
    Thanks again

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vlookup help please (XP SP2)

    Both work equally well.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •