Help with Vlookup incorporating Offset

Thanks:  0
Likes:  0

# Thread: Help with Vlookup incorporating Offset

1. ## 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,

Crimlet

2. 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.
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())

## User Tag List

#### Posting Permissions

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