Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: lookup or macro to find and then get value...

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    for instance i want a formula or macro to lookup a value in say B5 and then find the match on another sheet and give me the value 5 cells over and 1 cell down (offset?).

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Not quite sure I follow completely, but if the lookup range is col A in Sheet2 the following worked for me:

    =OFFSET(INDIRECT("Sheet2!A"&MATCH(B5,Sheet2!A:A,0)),5,1)

    This doesn't seem very efficient to me, so if I can find a better solution, I'll repost.

    Bye,
    Jay

    EDIT: Should be

    =OFFSET(INDIRECT("Sheet2!A"&MATCH(B5,Sheet2!A:A,0)),1,5)


    [ This Message was edited by: Jay Petrulis on 2002-04-25 15:50 ]

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    A Mile High!!
    Posts
    241
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This will work too...

    =index(sheet2!a2:z100,match(b5,sheet2!a2:a100,false),5)

    I actually worked on this for something today!!
    Hope it works for you!!

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-25 15:48, EdE wrote:
    This will work too...

    =index(sheet2!a2:z100,match(b5,sheet2!a2:a100,false),5)

    I actually worked on this for something today!!
    Hope it works for you!!
    Make that...

    =INDEX(sheet2!A2:Z100,MATCH(B5,sheet2!A2:A100,0)+1,6)

    Note that INDEX has 1 advantage of OFFSET -- it's not volatile. See http://www.decisionmodels.com/calcsecretsi.htm.

    [ This Message was edited by: Mark W. on 2002-04-25 16:13 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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