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

    Got two sheets '1' & '2'. '1' is presentation, '2' extracts from various sources.

    Got a named range 'LookupHere' in '2' - say [C3-E20]. Column B has values from 'elsewhere' (B3:B20).

    In sheet '1' Cell A2 - I got a value I would like to lookup in sheet '2' in Col C, when found offset by 0,-1 and get the value from 'B'.

    How do I offset outside my named range??

    =vlookup(FindThis,LookupHere,OFFSET(1,0,-1),FALSE) does not work

    PS: I'm not so familiar with eg. MATCH and INDEX - maybe they would do a better job in this case??
    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
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    =INDEX(2!$B$2:$B$20,MATCH(A1,2!$C$2:$C$20,0))

    Should do the trick
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Thumbs up

    [quote name='pieterse' post='761381' date='25-Feb-09 15:06']Hi,

    =INDEX(2!$B$2:$B$20,MATCH(A1,2!$C$2:$C$20,0))

    Should do the trick[/quote]


    Not quite - I get a #NAME error.

    Does the value I'm looking for have to be unique??
    In the range c2-c20 there are multiple occurences of the value...
    - so I'm planning to build the formula into a VBA loop sequence.

    Case is:
    sheet 1
    - got a list of person names,

    sheet 2
    - got a large range, all sorts of values,
    - 1 row per company/client,
    - col B contains 'Company/Client name'
    - col C is person name


    On sheet 1 - I must create a list
    Looping through the list of 'Person names'.
    (First row is 'Person name' - underneath I show each of his companies/clients)
    End loop

    Is it more clear what I try to achieve this way...?
    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Please attach a workbook with some sample data and an example of the desired output

  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts
    [quote name='Henrik Ryberg' post='761402' date='25-Feb-09 12:46']Not quite - I get a #NAME error.[/quote]

    To get this to work
    =INDEX(2!$B$2:$B$20,MATCH(A1,2!$C$2:$C$20,0))

    rename your sheet 2 to '2' (without quotes)

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Tried to make a sample workbook to the best of my humble ability and modified the formula as 'Gfamily' suggested - still no luck...
    Attached Files Attached Files
    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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    There are three problems:
    1. The names of the sales persons are "SalesPerson 1" etc. on Sheet 1, but "SalesPerson1" etc. on Sheet2, so you'll never get a match.
    2. The lookup range LookupHere should be a single column - it should refer to Sheet2!$E$9:$E$40
    3. MATCH and INDEX will return only the first instance found.
    If you correct 1. and 2., the first formula will work, but the others will return the same value.

    I'd suggest using Microsoft Access -you're basically trying to build queries on several tables, so Access is much more suitable for this than Excel.

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

    #1 - is a typo on my side, when making the example. It's not a problem in the real file.

    #3 - data originates from multiple extracts on various databases, so re-entering sounds a bit redundant to me

    #2 - hmmm, gotta think of another approach to this then. This specific problem is just a fragment of a larger set. Having one named column per item is a 'no go'. It will require to much maintenance over time.


    To Jan, Gfamily and Hans - I appreciate your efforts and inputs on this - Thanks.
    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
  •