A 0 is used to return an entire row/column. <<

Yes, I read that from Excel Help and might have seen it before. But I forgot about it when it was a *single row*. Thank you for reminding me. I also realize that I must highlight a *column* before I enter the formula with CSE if I want to return a *column*. If I highlight a *row* and the formula says return a *column*, I will get wrong result - the first value repeated.

From Excel Help:

"If each area in reference contains only one row or column, the row_num or column_num argument, respectively, is optional. For example, for a single row reference, use INDEX(reference,,column_num)."

So, it is official. But it doesn't say that I can leave out the comma and shift the position of the column_num argument to the left. Guess I learn from experiment.

I found something interesting with the IF( ).

A1: blank i.e. nothing keyed in =IF(A1,) returns FALSE.
A1: text i.e. letters =IF(A1,) returns #VALUE!
A1: numbers i.e. positive or negative but not zero =IF(A1,) returns 0
A1: 0 =IF(A1,) returns FALSE
A1: null string i.e. ="" =IF(A1,) returns #VALUE!

I can guess why 0 and blank return FALSE, but surprised that a number returns a TRUE condition but not letters and/or null.

No idea what's going on and *don't* plan to get to the bottom. But if anyone wants to comment, please feel free to do so.

This is my discovery for today. Thank you for reading.

Epinn

"JMB" wrote in message ...
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