LOOKUP Problems
 

Good morning.

I am trying to nest together some references and I can not get it to work out.

The formula below works fine:
=INDIRECT(ADDRESS(MATCH(R[-5]C,C9,0),3),1)

The problem is that I need the C9 to be dymanic. I have the following
formula:
=MATCH(R[-4]C,R[-11]C[-9]:R[-11]C[-3],0)+3

That will give me the 9 for the C9, but I do not know the sysntax to replace
the C9 in the original formula.
The first formula is in cell M16 and the second is in cell M14..if it is
relevant.

Thanks

LOOKUP Problems
 

James,

Can you not embed another INDIRECT (using text not ADDRESS)?

Try;

=INDIRECT(ADDRESS(MATCH(R[-5]C,INDIRECT("C"&(MATCH(R[-4]C,R[-11]C[-9]:R[-11]C[-3],0)+3)),0),3),1)

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"James McDowell" wrote:

Good morning.

I am trying to nest together some references and I can not get it to work out.

The formula below works fine:
=INDIRECT(ADDRESS(MATCH(R[-5]C,C9,0),3),1)

The problem is that I need the C9 to be dymanic. I have the following
formula:
=MATCH(R[-4]C,R[-11]C[-9]:R[-11]C[-3],0)+3

That will give me the 9 for the C9, but I do not know the sysntax to replace
the C9 in the original formula.
The first formula is in cell M16 and the second is in cell M14..if it is
relevant.

Thanks