Formula for 'Cell Address of the last numeric value within column' =ADDRESS(MATCH(9.99999999999999E+307, A:A), COLUMN(A1)) [Formula] The formula returns cell address of the last numeric value in column, which is $A$10. (Refer Table 1) 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] This part of the formula returns the row number of the last numeric value, within column, which is 10. (Refer Table 1). 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. ------------------------------------------------------------------------------------------------------------
Formula for 'Row number of the last numeric value within column'
=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. ---------------------------------------------------------------------------------------------------------
Formula for 'Row number of the last text value, including a formula blank, within column'
=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.----------------------------------------------------------------------------------------------------------
Formula for 'Last numeric value in column'
=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. ---------------------------------------------------------------------------------------------------------
Formula for 'Last numeric value in column'
=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. ----------------------------------------------------------------------------------------------------------
Formula for 'Last text value in column'
=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. ----------------------------------------------------------------------------------------------------------
Formula for 'Last text value in column'
=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. ---------------------------------------------------------------------------------------------------------
Formula for 'Last value in column, numeric or text' {=INDIRECT("A"&MAX(IF(NOT( ISBLANK(A:A)), ROW(A:A))))} [Formula] The formula returns last value in column, numeric or text, which is 22. (Refer Table 8). Returns value of highest row number in column A, using INDIRECT function for cell reference. Enter as an array formula: type the formula in the cell and then press CTRL+SHIFT+ENTER instead of just ENTER. Excel will automatically display the formula enclosed in braces { }. =IF(NOT(ISBLANK(A:A)), ROW(A:A)) [Formula Break] Returns FALSE if cell in column A is blank, else returns row number if NOT BLANK.{=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. --------------------------------------------------------------------------------------------------------
Formula for 'Last value in column, numeric or text' =INDIRECT("A"& SUMPRODUCT(MAX(( ROW(A:A))*( A:A<>"")))) [Formula] The formula returns last value in column, numeric or text, which is 22. (Refer Table 9). Returns value of highest row number in column A, using INDIRECT function for cell reference. Formula will not work if cells have error values like #REF!, #N/A, #DIV/0!, …. =(ROW(A:A))*( A:A<>"") [Formula Break] This part of the formula multiplies row number, with 1 in case cell in column A is non-blank and with 0 (zero) if cell is blank. Returns row number in case cell in column A is non-blank and 0 (zero) if cell is blank. =MAX((ROW(A:A))*( A:A<>"")) [Formula Break] This part of the formula returns largest row number of non-blank cell in column A, if entered as an array formula viz. {=MAX(( ROW(A:A))*( A:A<>""))}. If not an array formula, then formula =SUMPRODUCT(MAX(( ROW(A:A))*( A:A<>""))) will return the largest row number of non-blank cell in column A. =SUMPRODUCT(MAX(( ROW(A:A))*( 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 (Refer Table 9). ------------------------------------------------------------------------------------------------------
Formula for 'Last value in column, numeric or text'
=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!. ---------------------------------------------------------------------------------------------------------
Formula for 'Last Row number, numeric or text value'
=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. ---------------------------------------------------------------------------------------------------------
Formula for 'Last Row number, numeric or text value' =SUMPRODUCT(MAX(( ROW(A:A))*( A:A<>""))) [Formula] The formula returns last row number, numeric or text value, which is 10. (Refer Table 12). Returns the highest/largest row number viz. row number of the last non-blank cell in column A. Formula will not work if cells have error values like #REF!, #N/A, #DIV/0!, …. =(ROW(A:A))*(A:A<>"") [Formula Break] This part of the formula multiplies row number, with 1 in case cell in column A is non-blank and with 0 (zero) if cell is blank. Returns row number in case cell in column A is non-blank and 0 (zero) if cell is blank. =MAX((ROW(A:A))*( A:A<>"")) [Formula Break] Returns largest row number of non-blank cell in column A, if entered as an array formula viz. {=MAX(( ROW(A:A))*( A:A<>""))}. If not an array formula, then formula =SUMPRODUCT(MAX(( ROW(A:A))*( A:A<>""))) will return the largest row number of non-blank cell in column A. ------------------------------------------------------------------------------------------------------
Formula for 'Last Row number, numeric or text value' {=MAX( ROW(A:A)*( A:A<>""))} [Formula] The formula returns last row number, numeric or text value, which is 10. (Refer Table 13). Returns the highest/largest row number viz. row number of the last non-blank cell in column A. Formula will not work if cells have error values like #REF!, #N/A, #DIV/0!, …. Enter as an array formula. To enter a formula as an array formula, type the formula in the cell and then press CTRL+SHIFT+ENTER instead of just ENTER. Excel will automatically display the formula enclosed in braces { }. =(ROW( A:A))*( A:A<>"") [Formula Break] This part of the formula multiplies row number, with 1 in case cell in column A is non-blank and with 0 (zero) if cell is blank. Returns row number in case cell in column A is non-blank and 0 (zero) if cell is blank.
