using OFFSET(INDEX(MATCH)))
 

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?

using OFFSET(INDEX(MATCH)))
 

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" wrote in message
s.com...
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?

using OFFSET(INDEX(MATCH)))
 

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

On Sep 25, 1:24 pm, "Peo Sjoblom" wrote:
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" wrote in message

s.com...



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?- Hide quoted text -

- Show quoted text -

using OFFSET(INDEX(MATCH)))
 

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


--


Regards,


Peo Sjoblom



"Dave F" wrote in message
ups.com...
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

On Sep 25, 1:24 pm, "Peo Sjoblom" wrote:
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" wrote in message

s.com...



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?- Hide quoted text -

- Show quoted text -