I was curious about the use of Index with a
blank 2nd argument. So breaking it down,
I entered data into cells A1 thru A9, then in
another cell entered: =INDEX(A1:A9,)
This always returned the data from the 3rd
cell (A3) Can anyone tell me why please?
Enter these values in A1:A9 - 10,20,30,40,50,60,70,80,90
The reason you got the result from cell A3 is because you (initially)
entered the formula in some cell on ROW 3. Try this, enter the formula in
cell C1 and copy down to C9. Notice the pattern of results.
Now, enter the formula in cell A15. What happened? Why is it now returning
an error?
Let's start with a general overview of function arguments then we'll get
into the mechanics of the formula(s).
Functions return results based on what the particular function does and
based on the arguments the function processes. Some arguments are mandatory
and some are optional depending on the particular function. If you have
Excel version 2002 or later and you have Function Tool Tips enabled, when
you start typing in a formula Excel shows you the syntax of the functions
used in the formula. For INDEX it would look like this:
INDEX(array,row_num,[column_num])
INDEX(reference,row_num,[column_num],[area_num])
The arguments are separated by commas (or semi-colons depending on your
regional settings) and the optional arguments, if any, are enclosed in the
[ ] brackets. These arguments can be either defined, empty or omitted.
A defined argument is simply an argument that specifies the conditions or
parameters of that argument. For example:
=INDEX(A1:A9,5)
5 is the row_num argument so that argument is defined.
An empty argument is one that has been "allocated" but hasn't been defined.
As noted before, arguments are separated by commas so, if a comma appears
then it is assumed that the argument has been "allocated" and what follows
the comma is an argument. If the argument has been "allocated" but it isn't
defined then it's evaluated as being empty. So, in your test formula:
=INDEX(A1:A9,)
The array_ argument has been defined, A1:A9. It's followed by a comma
meaning that what follows is an argument, the row_num argument. The row_num
argument has been "allocated" but it's not defined so it's empty. An empty
argument will be evaluated as 0. If we were to include that argument:
=INDEX(A1:A9,0)
=INDEX(A1:A9,)
Both of those do/mean the same thing.
Now we need to understand omitted arguments. Omitted arguments are the
[optional_arguments] that some functions have. If you don't use the optional
arguments some of them have default parameters. That is, even if you don't
define the optional argument it will evaluate to a default setting. These
parameters and the default setting depend on the specific function.
Let's look at the test INDEX function again and compare it to the syntax:
INDEX(array,row_num,[column_num])
INDEX(A1:A9,)
In the test formula we've got the defined array_argument and the empty
row_num argument, both of which are mandatory arguments. We didn't use the
optional column_num argument so in this application it defaults to 0.
OK, now let's take a look at the mechanics of the formula.
INDEX(A1:A9,)
INDEX(A1:A9,0)
In this application we're using a one dimensional array as the
array_argument. The indexed array values are considered to be in a relative
sequential position. That is:
A1 = position 1
A2 = position 2
A3 = position 3
A4 = position 4
etc
etc
A9 = position 9
If the indexed array was C22:C25 the relative sequential positions would be
the same:
C22 = position 1
C23 = position 2
C24 = position 3
C25 = position 4
Typically, we use INDEX to return a value from the array_argument and we use
the row_num and/or the column_num argument to define which value from the
array we want returned. When the array is a vertical one dimensional array
(as in the test formula) we would use the row_num argument to define which
value we want.
=INDEX(A1:A9,5)
Return the value from position 5 of the range (indexed array) A1:A9.
Ok, that's pretty simple. So, how do these empty arguments come into play
here and what exactly do they do?
Remember, an empty argument evaluates to 0. However, if the indexed array is
considered to have relative sequential positions starting with 1, what does
0 do? There is no relative position 0. In this application 0 means to return
the *entire* indexed array. So:
=INDEX(A1:A9,)
Returns the entire array of values from A1:A9. Since the formula is
returning an array of values we need either another function that can handle
an array of values for further processing or we need to enter the formula in
an array of cells. However, if the formula is entered in a single cell that
cell can only hold a single result. This is what you did, entered the
formula in a single cell. Depending on where the formula cell is located and
how the formula was entered will determine what result the test formula
returns.
This is the factor that led to you entering the formula in a cell and
getting the value form A3. You had to have initially entered the formula in
a cell on ROW 3. Because the formula, as written, is returning an array of
results but wasn't using another function to further process the array of
results, and/or the formula wasn't entered in an array of cells, you got the
result that corresponds to the implicit intersection of the indexed array
and the location of the formula cell.
Let's assume the formula was normally entered in cell C3. Cell A3 is within
the indexed array and cell C3 forms a direct intersection to cell A3. So,
the result of the formula is the value from cell A3. This is the reason you
got the results you did when I suggested (at the very beginning of this
post) you enter the formula in C1 and copy down to C9. The formula entered
in A15 returned an error because cell A15 is not within an implicit
intersection of the indexed array *and* the formula was not entered as an
array.
If you initially enter the formula within an implicit intersection and then
move the formula by dragging to a location outside the implicit intersection
the result will be the same until Excel recalculates.
Ok, now let's look at TM's formula and see how it works.
Let's assume this is the data:
A1 = 1
A2 = 2
A3 = 3
B2 = 3
=OR(INDEX(A1:A3=B2,))
A1:A3=B2 will return an array of either TRUE or FALSE:
1 = 3 = FALSE
2 = 3 = FALSE
3 = 3 = TRUE
These logical values make up the array_argument to INDEX:
INDEX({FALSE;FALSE;TRUE},)
Because the row_num argument is empty it's evaluated as 0 so INDEX returns
the *entire* array of logical values. The array of logical values is passed
to the OR function:
OR({FALSE;FALSE;TRUE})
OR can handle arrays so this is why the formula doesn't have to be array
entered**. OR evaluates the array of logical values and if *any* of them are
TRUE then the result of OR = TRUE. If none of the logical values is TRUE
then OR = FALSE.
So:
=OR(INDEX(A1:A3=B2,))
=TRUE
Array entered vs. normally entered...
The test formula as written is returning an array of results:
INDEX(A1:A9,)
As noted before we either have to pass those results to another function for
further processing or array enter** the formula in an array of cells the
same size as the indexed range A1:A9 = 9 cells.
To array enter the formula we would select the range of cells say, C1:C9,
type the formula =INDEX(A1:A9,) but instead of hitting the Enter key like
you normally would we would use a combination of keys, CTRL,SHIFT,ENTER.
That is, hold down both the CTRL key and the SHIFT key then hit the ENTER
key.
With this particular formula the results would be the same as normally
entering the formula (just hit Enter) in cell C1 and drag copying down to
C9.
Now, remember that formula that's in cell A15? It's currently returning an
error because the formula is returning an array of results but the formula
is in a single cell and this cell is not within an implicit intersection.
Try this...
Select cell A15
Hit function key F2
Now, array enter the formula. Hold down both the CTRL key and the SHIFT key
then hit the ENTER key.
See what happens?
The formula now returns the *first* result of the indexed array.
Well, that's all I got!
exp101
--
Biff
Microsoft Excel MVP
T. Valko said:
Ok, this is going to take me a while (real slow typer!) so check back
later this evening.
.
