JMB,

Thank you so much. I have started my own thread "Interpreting comma where an optional argument is" because I don't want to "hijack" this thread. Hope GMW won't mind the extra details. I guess after all the formula I suggested for him/her works well.

First value/stock price

{=INDEX(1:1,MATCH(TRUE,(ISNUMBER(1:1)),0))} array formula i.e. ctrl+shift+enter

Epinn

"JMB" wrote in message ...
If you have a vertical array like this in A1:A4

1
2
3
4

=INDEX(A1:A4, 3)
=INDEX(A1:A4, 3, 1)

return the same element. The column number is optional because there is
only one column. Same thing for data containing only one row.

1 2 3 4

=INDEX(A1:D1, 3)
=INDEX(A1:D1, 1, 3)

except the row number is optional.

Optional means the argument does not have to be supplied. Putting in a
comma means you have supplied the argument, but it is empty (or blank).

Consider the If statement
=IF(A1=3, "A1 is 3") returns FALSE (for a false condition)
=IF(A1=3, "A1 is 3",) returns 0 (for a false condition)

or
=IF(A1=3,,) will return 0 for both True and False conditions.

or
=IF(A1=3,) will return 0 for True condition and FALSE for false condition.

Because for IF statements, the true conditional is required, but it can be
empty (or blank). The false conditional is optional and will return FALSE if
omitted. If it is empty (or blank as the help file says) it will return 0.