OFFSET vs INDEX(MATCH(...))
 

The two following formulas return the same result:

=OFFSET(INDEX('G:\NADC Finance\David Friedman\P2P MAPPING\[Copy of
MAPPING TABLE.xls]MAPPING_ACCOUNTS'!$E$1:$E$1050,MATCH(B2,'G:\NADC
Finance\David Friedman\P2P MAPPING\[Copy of MAPPING
TABLE.xls]MAPPING_ACCOUNTS'!$E$1:$E$1050,0)),,-2)

and

=INDEX('G:\NADC Finance\David Friedman\P2P MAPPING\[Copy of MAPPING
TABLE.xls]MAPPING_ACCOUNTS'!$C$1:$C$1050,MATCH(B2,'G:\NADC Finance
\David Friedman\P2P MAPPING\[Copy of MAPPING
TABLE.xls]MAPPING_ACCOUNTS'!$E$1:$E$1050,0))

However, when the external workbook is closed, the formula using
OFFSET returns #VALUE! errors and the other does not. Is this because
OFFSET is a volatile function?

Thanks,

Dave

OFFSET vs INDEX(MATCH(...))
 

Wouldn't think it is because it is volatile, SUMIF is not volatile but it
doesn't work on closed projects, whereas SUMPRODUCT does. I would guess it
is just down to the coder.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Dave F" wrote in message
...
The two following formulas return the same result:

=OFFSET(INDEX('G:\NADC Finance\David Friedman\P2P MAPPING\[Copy of
MAPPING TABLE.xls]MAPPING_ACCOUNTS'!$E$1:$E$1050,MATCH(B2,'G:\NADC
Finance\David Friedman\P2P MAPPING\[Copy of MAPPING
TABLE.xls]MAPPING_ACCOUNTS'!$E$1:$E$1050,0)),,-2)

and

=INDEX('G:\NADC Finance\David Friedman\P2P MAPPING\[Copy of MAPPING
TABLE.xls]MAPPING_ACCOUNTS'!$C$1:$C$1050,MATCH(B2,'G:\NADC Finance
\David Friedman\P2P MAPPING\[Copy of MAPPING
TABLE.xls]MAPPING_ACCOUNTS'!$E$1:$E$1050,0))

However, when the external workbook is closed, the formula using
OFFSET returns #VALUE! errors and the other does not. Is this because
OFFSET is a volatile function?

Thanks,

Dave