INDEX useing Dynamic Range
 

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

INDEX useing Dynamic Range
 

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



"Vic Waller" wrote in message
. 131...
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

INDEX useing Dynamic Range
 

Maybe:

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


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=123934

INDEX useing Dynamic Range
 

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" wrote:


Maybe:

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


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=123934

INDEX useing Dynamic Range
 

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

Tom Hutchins;448166 Wrote:
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" wrote:


Maybe:

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


--
NBVC

Where there is a will there are many ways.

'The Code Cage' ('The Code Cage - Microsoft Office Help - Microsoft
Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
NBVC's Profile: 'The Code Cage Forums - View Profile: NBVC'
(http://www.thecodecage.com/forumz/members/nbvc.html)
View this thread: 'INDEX useing Dynamic Range - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=123934)




--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=123934