Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Montreal, Quebec, Canada
    Posts
    261
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Some kind a look up table (2000)

    The short version: How the heck do I create a table whereby Excel will look up the value of a cell and ... OK, I don't know how to write a short version of this request. Maybe by way of example:

    <table border=0>
    <td>[b]Country</td><td>[b]CountryCode</td><td>[b]Capital</td><td>[b]CurrencyCode</td><td>ExRate</td><td>Afghanistan</td><td>AF</td><td>Kabul</td><td>AFA</td><td>.0221</td><td>Albania</td><td>AL</td><td>Tiran</td><td>ALL</td><td>.00354</td><td>Algeria</td><td>DZ</td><td>Algiers</td><td>DZD</td><td>.0114</td></table>

    The user picks a country from a dropdown list. In the next cell, Excel uses the table above to look up and display the CurrencyCode; beside that, the ExRate.

    The long version: OK, I've given up. I've been going through the help files and sample spreadsheets, but I just don't know how to formulate the question, let alone the formula. The idea is to create an expense claim for people who travel to multiple countries (with multiple currencies). I want them to select the country and have Excel look up the currency code and the apply the exchange rate. It should be simple, but between ARRAYs, INDEXes, MATCHes and more LOOKUPs than I care to imagine, I'm hopelessly lost. Can someone point me in the right direction?

    Thanks in advance!

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Some kind a look up table (2000)

    See if this helps.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Some kind a look up table (2000)

    John already posted an Excellent link. Perhaps the attached workbook can help too.

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Some kind a look up table (2000)

    Jezsik,

    Here is a real quick attempt. The combobox is linked to range Tables!A2:A5 for the list items. The combobox linked cell property is set to A1. The other cells do a vlookup based upon the value in A1.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Montreal, Quebec, Canada
    Posts
    261
    Thanks
    0
    Thanked 0 Times in 0 Posts

    What about this alpha-ordering thing?

    OK, I have my tables all set up and the VLOOKUP is working pretty well. Now I'm curious about something. The instructions for VLOOKUP say that the table should be sorted in ascending order. What's up with that?

    For my validation dropdown list box (I just don't know enough vbasic to manage the coding), I don't want people folks to have to scroll though Afghanistan, Albania and Algeria before they get to USA. I want to put the frequently used countries at the top of the list. Can I expect trouble from VLOOPUP over this? If so, what options do I have now?

    Bonus question: Is there any way to adjust the width of the dropdown list box for validation? It's wayyyy too short.

    Thanks for all the help so far!

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: What about this alpha-ordering thing?

    You need to add the last (optional) parameter to the Vlookup and set it FALSE. Then the list can be in any order, since it looks for an exact match. If you don't do this, you can get weird hits.

    Datavalidation can not change the length. You must use a combobox from FORMS (the next level) or controls toolbox (even more advanced). The more advanced, the more difficult to use, but the more control you have over the object.
    Steve

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: What about this alpha-ordering thing?

    Set the last argument in the =VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup) to FALSE; this will cause VLOOKUP() to only find exact matches, so the data does not need to be sorted.. (See Excel Help for a further explanation.)
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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