Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range Within INDEX | Excel Discussion (Misc queries) | |||
Dynamic Index() or Vlookup() | Excel Worksheet Functions | |||
Dynamic Range Using INDEX instead of Offset | Excel Discussion (Misc queries) | |||
dynamic table_array in lookup,match & index | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel |