Help with Vlookup incorporating Offset

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

Thread: Help with Vlookup incorporating Offset

  1. #1
    Board Regular
    Join Date
    May 2002
    Location
    Ipswich, Suffolk, England
    Posts
    136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help with Vlookup incorporating Offset

     
    I need help with a formula, I have tried the below but it does not work,

    =IF(ISNA(VLOOKUP($A5,INDIRECT("'"&B$1&"'!$a:$I"),offset(1,3),FALSE)),0,(VLOOKUP($A5,INDIRECT("'"&B$1&"'!$a:$I"),offset(1,3),,FALSE)))


    Can some one help, I want to lookup the reference in cell A5 then return the cell 1 row down 3 columns across,


    Thanks in advance

    Crimlet

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    VLOOKUP returns a value and OFFSET expects a reference as well as the row and column offset arguments. Try:

    =IF(ISNUMBER(MATCH($A5,INDIRECT("'"&B$1&"'!$A:$A"),0)),INDEX(INDIRECT("'"&B$1&"'!$A:$I"),MATCH($A5,INDIRECT("'"&B$1&"'!$A:$A"),0)+1,3),0)

    which returns the value one row down in the 4th column if it finds the contents of A5 in column A of the table.

    Correction: returns the value in the 3rd column. Change 3 to 4 to return the 4th column (which is what I did but forgot to repaste the formula).

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,489
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Since we have the phenomenon of "computing twice" and volatile INDIRECT (I'm referring to the Andrew posted, but this also holds for OFFSET) plust the fact that the target workbook must be open for the lookup to work, I'd suggest using morefunc functions...

    1] Replace INDIRECT with INDIRECT.EXT which allows you to use closed target workbooks;

    2] Change the formula Andrew provided to...

    =IF(ISNA(SETV(INDEX(INDIRECT.EXT("'"&B$1&"'!$A:$I"),MATCH($A5,INDIRECT.EXT("'"&B$1&"'!$A:$A"),0)+1,3))),0,GETV())
    Assuming too much and qualifying too much are two faces of the same problem.

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
  •  

 

 
DMCA.com