INDEX useing Dynamic Range

[Vic Waller]

I want to use INDEX with one part of the lookup range as a dynamic address
that I generate in a series of cells i.e.

MATCH(xxxx:A14093,278,3)

I cant get it to work because if the generated address such as A984 is
created in cell M1 and I put M1 were the xxxx is, the range interpreted by
MATCH is M1:A14093 not my desired A984:A14093. If I use INDIRECT(M1) I get
the contents of A984 not the address A984.

Any help will be much appreciated.

Vic

 

[JP Ronse]

Hi Vic,

Try this...

If cell M1 is containing the start address of your range, replace xxxx with
indirect(M1).

Are you sure about the use of the match function: lookup_up_value, array,
type.

Wkr,

JP

 

[NBVC]

Maybe:

=MATCH(278,INDIRECT(M1&":A14093"),3

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[Tom Hutchins]

This works but may not return an exact match. 3 is not a valid entry for the
final parameter fo MATCH, and works as if 1 was used or it was omitted
entirely. This will find the largest value that is less than or equal to 278.
For an exact match only, use zero for the final argument.

Hope this helps,

Hutch

 

[NBVC]

You are correct, that 3 should be 0 for an exact match... I think I jus
blindly copied the OP's formula without noting that required change.
not sure what the 3 is for?

This works but may not return an exact match. 3 is not a valid entry fo
the
final parameter fo MATCH, and works as if 1 was used or it was omitted
entirely. This will find the largest value that is less than or equa
to 278.
For an exact match only, use zero for the final argument.

Hope this helps,

Hutch


Office Discussion' (http://www.thecodecage.com))

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com