Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup trivia... (XL 2003 UK)

    Hi,

    Probably a trivial one...

    Got two columns:
    'A' holds name of countries: US, UK, Spain, Denmark, Italy, Portugal, Netherlands etc...
    'B' holds short equivalent ISIN codes: US, GB, ES, DK, IT, PT, NL...

    Together this gives a sorted range from A1-B7 (sorted ascending by col 'A')


    In columns D and E rows 1-4 I have the following:
    D1, value: 'ISIN'
    D2, formula: =VLOOKUP(E2,A1:B7,2,FALSE) (This is the result of the 'Country lookup)
    D3, value: 'ISIN lookup'
    D4, validation list. (set to: 'List', Source: =$B$1:$B$7)

    E1, value: 'Country lookup'
    E2, validation list. (set to: 'List', Source: =$A$1:$A$7)
    E3, value: 'Country'
    E4, ...????? (This should be the result of the country lookup)


    My problem is that Excel looks in the leftmost column and that the data I am looking up not are placed in the leftmost of the two columns.
    - I am hoping that there are a simple work around for a situation like this...

    Bests and thanks in advance,
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

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

    Re: Lookup trivia... (XL 2003 UK)

    You can use a combination of MATCH and INDEX:
    <code>
    =INDEX(A1:A7,MATCH(D4,B1:B7,0))</code>

  3. #3
    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: Lookup trivia... (XL 2003 UK)

    Check out the StarPost Re: Vlookup to the left? (Excel: 97-2002) for a technique with INDEX and MATCH.

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup trivia... (XL 2003 UK)

    Hi Steve and Hans,

    Thanks both of you for your prompt replies, worked like a charm - as always

    ;o)
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

Posting Permissions

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