Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX function (array type)
Maybe I'm missing a trick here.
The formula =INDEX({1,2,3},1,2) will return the value 2 The formula =INDEX(A1,1,2) where the cell A1 contains the array constant ={1,2,3} will return a #REF! error Why does this happen and what is wrong with referencing a array in a cell? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX function (array type)
you are referencing values rather than hard-coding them. In this case, the
values are treated as text rather than an array. There's usually an easier way around this issue if you're trying to calculate something... "Sean" wrote: Maybe I'm missing a trick here. The formula =INDEX({1,2,3},1,2) will return the value 2 The formula =INDEX(A1,1,2) where the cell A1 contains the array constant ={1,2,3} will return a #REF! error Why does this happen and what is wrong with referencing a array in a cell? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX function (array type)
Hi,
the arguments 1 and 2 in both examples refers to the first row, second column. Now the second column when referencing a spreadsheet cell is one column to the right of the reference, here with A1:B1 as the reference, B1 is the second column. Excel does not look inside of the cell and take the second entry. There is also a second issue, since A1 is a one column reference asking for the second column is asking for a column outside the reference range, again that is column B -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Sean" wrote: Maybe I'm missing a trick here. The formula =INDEX({1,2,3},1,2) will return the value 2 The formula =INDEX(A1,1,2) where the cell A1 contains the array constant ={1,2,3} will return a #REF! error Why does this happen and what is wrong with referencing a array in a cell? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX function (array type)
I understand what you're saying Shane, however I an trying to use the
Array form of the INDEX() function and not the Reference form. Sean On Aug 27, 9:42*pm, Shane Devenshire wrote: Hi, the arguments *1 and 2 in both examples refers to the first row, second column. *Now the second column when referencing a spreadsheet cell is one column to the right of the reference, here with A1:B1 as the reference, B1 is the second column. *Excel does not look inside of the cell and take the second entry. * There is also a second issue, since A1 is a one column reference asking for the second column is asking for a column outside the reference range, again that is column B -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Sean" wrote: Maybe I'm missing a trick here. The formula *=INDEX({1,2,3},1,2) will return the value 2 The formula *=INDEX(A1,1,2) where the cell A1 contains the array constant ={1,2,3} will return a #REF! error Why does this happen and what is wrong with referencing a array in a cell? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX function (array type)
Yes, there probably is an easier way around this but that doesn't
solve my primary dilemma. If (as you say )the Array constant in cell A1 is being treated as text instead of an array, is there a way to force it to be treated as such. Something like what the INDIRECT() function does for address strings? S. On Aug 27, 9:25*pm, Sean Timmons wrote: you are referencing values rather than hard-coding them. In this case, the values are treated as text rather than an array. *There's usually an easier way around this issue if you're trying to calculate something... "Sean" wrote: Maybe I'm missing a trick here. The formula *=INDEX({1,2,3},1,2) will return the value 2 The formula *=INDEX(A1,1,2) where the cell A1 contains the array constant ={1,2,3} will return a #REF! error Why does this happen and what is wrong with referencing a array in a cell? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help on Array and Index function | Excel Worksheet Functions | |||
Indirect function in Index/Match Array | Excel Discussion (Misc queries) | |||
Returning an array from the INDEX function | Excel Worksheet Functions | |||
Using INDEX function to return array row. | Excel Worksheet Functions | |||
Looking for formula index/match-type that returns an array | Excel Worksheet Functions |