Help with Vlookup incorporating Offset

[crimlet]

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

 

[Andrew Poulsom]

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

 

[Aladin Akyurek]

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