Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 174
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,069
Default 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


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamic Range Within INDEX BugOnPoint Excel Discussion (Misc queries) 3 July 3rd 09 10:42 PM
Dynamic Index() or Vlookup() Frank Pytel Excel Worksheet Functions 1 November 1st 08 06:35 PM
Dynamic Range Using INDEX instead of Offset [email protected] Excel Discussion (Misc queries) 4 September 19th 07 06:16 AM
dynamic table_array in lookup,match & index Salman Excel Worksheet Functions 1 November 20th 06 08:28 AM
Dynamic Range with unused formula messing up x axis on dynamic graph [email protected] Charts and Charting in Excel 2 February 2nd 06 09:02 PM


All times are GMT +1. The time now is 08:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"