Aladin : 12492.html

Re: Aladin : 12491.html

> Hi Chris,

> The formula" in thread 12492 is brilliant !

You're kind, thanks.

> How does it work ? I don't follow the INDEX function which only has 2 bits in it - when I look at INDEX via the paste function button it asks for 3 bits :-(

If you look at Index under Help, you'll find the following description when you feed an "array", that's, a range in a column or row:

QUOTE --

Syntax 1
Array form
INDEX(array,row_num,column_num)
Array is a range of cells or an array constant. For more information about values in array constants, click .
Row_num selects the row in array from which to return a value. If row_num is omitted, column_num is required.
Column_num selects the column in array from which to return a value. If column_num is omitted, row_num is required.
¥ If both the row_num and column_num arguments are used, INDEX returns the value in the cell at the intersection of row_num and column_num.
¥ If array contains only one row or column, the corresponding row_num or column_num argument is optional.
¥ If array has more than one row and more than one column, and only row_num or column_num is used, INDEX returns an array of the entire row or column in array.

UNQUOTE --

My use is directly related to:

"If array contains only one row or column, the corresponding row_num or column_num argument is optional," which enables you to just enter 2 args and Excel is able to derive from the 1st what the 2nd arg to INDEX means.

> And the quinti-trilli-gazillion number 9.99999999+E207, I don't understand : is this to anticipate MS Excel finally upgrading its row limit of 65,000 to infinity ?

No, that astronomical number is not related to the row limit.

Rather: MATCH returns the pos (row or column number, depending on whether you use say A:A or say 2:2) of the last numeric cell if it fails to find a match for a numeric lookup value. Since this huge number is practically impossible to occur in a row or column of numeric type, the MATCH function is bound to return the pos of the last numeric cell. I'd not recommend Astromers or Particle Physicists to use this constuction to find the row/column number of the last numeric cell, otherwise I believe it's OK.

BTW, Happy New Year.

Aladin

=======

Re: Aladin : 12491.html

Wow, that's clever..

no, wait, that's *very* clever !

Thanks for the explanation.

Let's hope the astrophysicists don't use that formula, they might just find Schrodinger's cat ;-)

thanks for your patience Aladin
Chris