using OFFSET(INDEX(MATCH)))

[Dave F]

I have the following formula:

=OFFSET(INDEX('Capital Summary'!F$25:F$28,MATCH(A4,'Capital Summary'!F
$25:F$28,0)),,1)

This works fine, however, when I remove the OFFSET(...) part and just
have

=INDEX('Capital Summary'!F$25:F$28,MATCH(A4,'Capital Summary'!F$25:F
$28,0))

the matched value is returned, not its cell address. How does OFFSET
pick up the cell address?

 

[Peo Sjoblom]

It doesn't pick up the cell address, it picks up the value that is one cell
to the right of the looked up value

This will do the same and is more efficient

=INDEX('Capital Summary'!G$25:G$28,MATCH(A4,'Capital Summary'!F$25:F$28,0))


If you want the cell address you can use

=CELL("address",INDEX('Capital Summary'!F$25:F$28,MATCH(A4,'Capital
Summary'!F$25:F$28,0)))



--


Regards,


Peo Sjoblom

 

[Dave F]

This is very helpful thanks.

When you say that your formula is more efficient do you mean that it
does not use a volatile function?

Dave

 

[Peo Sjoblom]

Yes and one less function call, however the CELL formula I posted is
volatile.


--


Regards,


Peo Sjoblom