First, for clarification, the help file uses the wording "blank argument" but
I think it could be confusing since the empty string "" is often referred to
as "blank". I use the term empty, which may not be right, it just helps me
keep it straight in my mind.

=INDEX(A1:D1,0,3)

A 0 is used to return an entire row/column. In this case, the function
returns all rows of the third column. Since there is only 1 row, you still
only get one element. A1:D3, however would return 3 elements and would need
to entered as an array formula to see all of the elements.

=INDEX(A1:D1,,3)

Never tried it this way. Perhaps designed to treat an empty/blank argument
the same as if it were omitted (unlike the IF function).



"Epinn" wrote:

To all:

This is a continuation of the discussion from the following thread.

http://groups.google.ca/group/micros...a7538d98438064

or http://tinyurl.com/wj2s3

JMB,

Thank you for taking the time to help me solve my mystery plus *more*. I appreciate it very much.

I had no problem dealing with "optional" when INDEXing on a single *column* because the column number comes *after* the row number in the syntax. But I was quite confused on *row* because of the position of the row_number in the syntax. I was quite surprised that a comma was not needed where the row_number was called for.

Putting in a comma means you have supplied the argument, but it is empty (or blank). <<

This is the truth. I have always been under the impression (at least with other programming languages) that comma (in this context) means I am omitting the argument which is optional. I never thought of it the way you described it. This is interesting but at the same time a bit of a problem for me. This is simply because I don't know how to translate "empty (or blank)" into the function. As you said, it is Excel Help terminology and I should get used to it. But I can translate a zero into the function and I think I may be more ready to accept "...... but it is *zero*."

=INDEX(A1:D1,0,3) and
=INDEX(A1:D1,,3)
return the same result as =INDEX(A1:D1,3) and =INDEX(A1:D1,1,3).

I am surprised that =INDEX(A1:D1,0,3) works and doesn't give me an error.

Trying to translate "empty or blank" ......
=INDEX(A1:D1,"",3) or =INDEX(A1:D1," ",3) of course will give the #VALUE! error. <G
"" is null and not even blank.

For the record, my true confusion was that =INDEX(A1:D1,3) worked without me placing a comma where the row_number was supposed to be. Now I know Excel is pretty flexible and that it will take care of all four versions listed above. Interesting!

Your illustration of IF( ) and comma is precious. I didn't even know that I could put two comma's in the IF function.

Once again, thanks for your guidance. Look forward to chatting with you again.

Epinn