using OFFSET(INDEX(MATCH)))

  • Thread starter Thread starter Dave F
  • Start date Start date
D

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?
 
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
 
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
 
Yes and one less function call, however the CELL formula I posted is
volatile.


--


Regards,


Peo Sjoblom
 
Back
Top