1. ## 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

2. ## Re: vlookup help please (XP SP2)

Steve,
Could you provide an attachment?
Thanks.

3. ## 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?

4. ## 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

5. ## 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. ## 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. ## 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

8. ## 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
•