

=MATCH(9.99999999999999E+307, A:A) [Formula] The formula returns row number of the last numeric value in column, which is 10. (Refer Table 2). The range doesn't have to be a full column. You can use A5:A50 instead of A:A. MATCH seemingly looks for a numeric value at the bottom of the column, presuming the range is in ascending order, and ignores text & non-numeric values. MATCH looks for 9.99999999999999E+307 in the column, and in the absence of Match_type 0 (to find an exact match), it looks for the largest value that is less than or equal to 9.99999999999999E+307. It then determines that the last numeric in the column is the largest value that is less than or equal to 9.99999999999999E+307. MATCH returns the relative position in the Look-Up Array, of the last numeric value (viz. numeric value at the bottom of the column) which will obviously be smaller than or equal to this largest. The formula works even if the range contains blanks, error values (like #N/A), and text values. If the range has no numeric value, the MATCH formula will return the #N/A error.
9.99999999999999E+307 [Formula Break] This is the Largest +ive number in Excel. ---------------------------------------------------------------------------------------------------------

=MATCH(REPT("z", 255), A:A) [Formula]{Presuming no Greek, Cyrillic, Hebrew, or Arabic characters in text values} The formula returns row number of the last text value in column, which is 9. (Refer Table 3). The range doesn't have to be a full column. You can use A5:A50 instead of A:A. MATCH seemingly looks for a text value at the bottom of the column, presuming the range is in ascending order, and ignores numerics & non-text values. MATCH looks for the string of 255 z's in the column, and in the absence of Match_type 0 (to find an exact match), it looks for the largest value that is less than or equal to 255 z's. It then determines that the last string in the column is the largest value that is less than or equal to 255 z's. MATCH returns the relative position in the Look-Up Array, of the last text value (viz. text value at the bottom of the column) which will obviously be smaller than or equal to this largest. The formula works even if the range contains blanks, error values (like #N/A), and numerical values. If the range has no text value, the MATCH formula will return the #N/A error.
=REPT("z",255) [Formula Break] In Excel this evaluates to the ‘largest’ string value, consisting of 255 ‘Z’ characters (zzzzzzzz ......), to find the last text entry. Greek, Cyrillic, Hebrew, or Arabic characters come after "z" in the sort order. Hence the exclusion.----------------------------------------------------------------------------------------------------------

=INDEX(A:A, MATCH(9.99999999999999E+307, A:A)) [Formula]
The formula returns last numeric value in column, which is 22. (Refer Table 4). The range doesn't have to be a full column. You can use A5:A50 instead of A:A.
INDEX Returns the numeric value in the array selected by the row number index. Row number is "=MATCH(9.99999999999999E+307, A:A)", as determined below. The formula works even if the range contains blanks, error values (like #N/A), and text values.9.99999999999999E+307 [Formula Break] This is the Largest +ive number in Excel. =MATCH(9.99999999999999E+307, A:A) [Formula Break] Returns row number of the last numeric value, within column, which is 10. (Refer Table 4). The range doesn't have to be a full column. You can use A5:A50 instead of A:A. MATCH seemingly looks for a numeric value at the bottom of the column, presuming the range is in ascending order, and ignores text & non-numeric values. MATCH looks for 9.99999999999999E+307 in the column, and in the absence of Match_type 0 (to find an exact match), it looks for the largest value that is less than or equal to 9.99999999999999E+307. It then determines that the last numeric in the column is the largest value that is less than or equal to 9.99999999999999E+307. MATCH returns the relative position in the Look-Up Array, of the last numeric value (viz. numeric value at the bottom of the column) which will obviously be smaller than or equal to this largest. If the range has no numeric value, the MATCH formula will return the #N/A error. ---------------------------------------------------------------------------------------------------------

=LOOKUP(9.99999999999999E+307, A:A) [Formula]
The formula returns last numeric value in column, which is 22. (Refer Table 5).
If the Look_Up value is greater than the largest value in the Look_Up Array, it will return the last number in the array, even if the Look_Up Array is not sorted in ascending order, which is required for the LOOKUP formula. LOOKUP seemingly starts from the bottom of the Look-Up Array and searches vertically up, presuming the range is in ascending order and the last number being the largest in the Look-Up Array. Hence 9.99999999999999E+307 has been arbitrarily chosen as the Look-Up value, which you know will be greater than the value you are looking for. The formula works even if the range contains blanks, error values (like #N/A), and text values. Where the value to be found is larger than any value of that particular type (viz. numeric or text) in the referenced value, the LOOKUP function seemingly returns the last listed item of that particular type.9.99999999999999E+307 [Formula Break] This is the Largest +ive number in Excel. ----------------------------------------------------------------------------------------------------------

=INDEX(A:A, MATCH(REPT("z", 255), A:A)) [Formula]{Presuming no Greek, Cyrillic, Hebrew, or Arabic characters in text values}
The formula returns last text value in column, which is "Humpty". (Refer Table 6). INDEX Returns the text value in the array selected by the row number index. Row number is "=MATCH(REPT("z",255),A:A)", as determined below.
=REPT("z",255) [Formula Break] In Excel this evaluates to the ‘largest’ string value, consisting of 255 ‘Z’ characters (zzzzzzz .....), to find the last text entry. Greek, Cyrillic, Hebrew, or Arabic characters come after "z" in the sort order. Hence the exclusion. The formula works even if the range contains blanks, error values (like #N/A), and numerical values.
=MATCH(REPT("z", 255), A:A) [Formula Break] Returns row number of the last text value, within column, which is 9. (Refer Table 6). The range doesn't have to be a full column. You can use A5:A50 instead of A:A. MATCH seemingly looks for a text value at the bottom of the column, presuming the range is in ascending order, and ignores numerics & non-text values. MATCH looks for the string of 255 z's in the column, and in the absence of Match_type 0 (to find an exact match), it looks for the largest value that is less than or equal to 255 z's. It then determines that the last string in the column is the largest value that is less than or equal to 255 z's. MATCH returns the relative position in the Look-Up Array, of the last text value (viz. text value at the bottom of the column) which will obviously be smaller than or equal to this largest. If the range has no text value, the MATCH formula will return the #N/A error. ----------------------------------------------------------------------------------------------------------

=LOOKUP(REPT("z", 255), A:A) [Formula]{Presuming no Greek, Cyrillic, Hebrew, or Arabic characters in text values} The formula returns last text value in column, which is "Humpty". (Refer Table 7). The range doesn't have to be a full column. You can use A5:A50 instead of A:A. LOOKUP seemingly starts from the bottom of the Look-Up Array and searches vertically up, presuming the range is in ascending order and the last text value being the largest in the Look-Up Array. LOOKUP looks for and returns a text value, and ignores numerics & non-text values. The formula works even if the range contains blanks, error values (like #N/A), and numerical values. Where the value to be found is larger than any value of that particular type (viz. numeric or text) in the referenced value, the LOOKUP function seemingly returns the last listed item of that particular type. =REPT("z",255) [Formula Break] In Excel this evaluates to the ‘largest’ string value, consisting of 255 ‘Z’ characters (zzzzzz .....), to find the last text entry. Greek, Cyrillic, Hebrew, or Arabic characters come after "z" in the sort order. Hence the exclusion. ---------------------------------------------------------------------------------------------------------

{=MAX(IF(NOT( ISBLANK(A:A)), ROW(A:A)))} [Formula Break] Returns the highest/largest row number viz. row number of the last non-blank cell in column A, which is 10. (ReferTable 8) Enter as an array formula. --------------------------------------------------------------------------------------------------------


=LOOKUP(2, 1/(LEN(A:A)>0), A:A) [Formula] The formula returns last value in column, numeric or text, which is 22. (Refer Table 10) The LOOKUP function searches for the Lookup_value "2" in the Lookup_vector which only has values of either 1 or #DIV/0!. Not finding this, it will match the last value that is less than or equal to the Lookup_value, which is the last "1", and return the corresponding value/entry in column A (Result_vector). =LOOKUP(2, 1/(LEN(A:A)>0), A:A) [Formula Break] The formula uses LOOKUP function in VECTOR form. The first parameter is Lookup_value, the second parameter is Lookup_vector and the third parameter is Result_vector. 1/(LEN(A:A)>0) [Formula Break] Second parameter of Lookup_vector -> in this formulation, the formula returns an array of values 1 or #DIV/0! viz. {1,1,#DIV/0!,1,#DIV/0!,…..}, value 1 indicating a non-blank cell and #DIV/0! indicating a blank cell. A non-blank cell returns TRUE with the function LEN(A:A)>0 and 1/TRUE returns the value 1, while a blank cell returns FALSE with the function LEN(A:A)>0 and 1/FALSE returns the value #DIV/0!. ---------------------------------------------------------------------------------------------------------

=LOOKUP(2, 1/(LEN(A:A)>0), ROW(INDIRECT("1:65536"))) [Formula] The formula returns last row number, numeric or text value, which is 10. (Refer Table 11) The LOOKUP function searches for the Lookup_value "2" in the Lookup_vector which only has values of either 1 or #DIV/0!. Not finding this, it will match the last value that is less than or equal to the Lookup_value, which is the last "1", and return the corresponding row number (Result_vector). "65536" in Indirect Function, indicates maximum number of rows in Excel 2003. Formula valid for pre2007 Excel versions. =LOOKUP(2, 1/(LEN(A:A)>0), A:A) [Formula Break] The formula uses LOOKUP function in VECTOR form. The first parameter is Lookup_value, the second parameter is Lookup_vector and the third parameter is Result_vector. 1/(LEN(A:A)>0) [Formula Break] Second parameter of Lookup_vector -> in this formulation, the formula returns an array of values 1 or #DIV/0! viz. {1,1, #DIV/0!,1, #DIV/0!,…..}, value 1 indicating a non-blank cell and #DIV/0! indicating a blank cell. A non-blank cell returns TRUE with the function LEN(A:A)>0 and 1/TRUE returns the value 1, while a blank cell returns FALSE with the function LEN(A:A)>0 and 1/FALSE returns the value #DIV/0!. ROW(INDIRECT("1:65536")) [Formula Break] In this formulation, the formula generates an array of consecutive integers, of row numbers. ---------------------------------------------------------------------------------------------------------

