Excel Tips

Last used row number and cell address, last numeric or text value in column

User Rating:  / 10
PoorBest 
Details
 
Find Last Used Row in a Column:

Related Links:
VBA Codes - Last used row; Last used column.

--------------------------------------------------------------------------------------------------------------------------------


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.

 


Worksheet Functions Used:

ADDRESS:
Creates a cell address as text, given specified row and column numbers.
Syntax: ADDRESS(row_num,column_num,abs_num,a1,sheet_text)
- Row_num   is the row number to use in the cell reference.
- Column_num   is the column number to use in the cell reference.
- Abs_num   specifies the type of reference to return: 1 or omitted - Absolute; 2 - Absolute row, relative column; 3 - Relative row, absolute column; 4 - Relative.
- A1   is a logical value that specifies the A1 or R1C1 reference style. If a1 is TRUE or omitted, ADDRESS returns an A1-style reference; if FALSE, ADDRESS returns an R1C1-style reference.
- Sheet_text   is text specifying the name of the worksheet to be used as the external reference. If sheet_text is omitted, no sheet name is used.

 

INDEX:
Returns the value of an element in a table or an array selected by the row and column number indexes.
Syntax: INDEX(array,row_num,column_num)
- Array   is a range of cells or an array constant.
- 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.

 

MATCH:
Returns the relative position of an item in an array that matches a specified value in a specified order.
Syntax: MATCH(lookup_value,lookup_array,match_type)
- Lookup_value is the value you want to match in lookup_array.
- Lookup_array   is a contiguous range of cells containing possible lookup values. Lookup_array must be an array or an array reference.
- Match_type   is the number -1, 0, or 1:
:- If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order.
:- If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order
:- If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order.
:- If match_type is omitted, it is assumed to be 1.
- MATCH returns the position of the matched value within lookup_array, not the value itself.
- MATCH does not distinguish between uppercase and lowercase letters when matching text values.

 

REPT:
Repeats text a given number of times.
Syntax: REPT(text,number_times) 

 

LOOKUP (Array Form):
Syntax: LOOKUP(lookup_value,array)
- Looks in the first row or column of an array for the specified value and return a value from the same position in the last row or column of the array
- If LOOKUP can't find the lookup_value, it uses the largest value in the array that is less than or equal to lookup_value.
- LOOKUP always selects the last value in the row or column.
- The values in array must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent.
- If array is square or is taller than it is wide (more rows than columns), LOOKUP searches in the first column.

 

ROW:
Returns the row number of a reference.
Syntax: ROW(reference)
- If reference is omitted, it is assumed to be the reference of the cell in which the ROW function appears..
- =ROW()  ->  Row in which the formula appears.
- =ROW(C10)  ->  Row of the reference (10).

MAX:
Returns the largest value in a set of values.
Syntax: MAX(number1,number2,...)
- Arguments can either be numbers or names, arrays, or references that contain numbers.
- If the arguments contain no numbers, MAX returns 0 (zero).

ISBLANK:
Checks whether a reference is to an empty cell, and returns TRUE or FALSE.
Syntax: ISBLANK(value)
- Value   is the value you want tested. Value can be a blank (empty cell), error, logical, text, number, or reference value, or a name referring to any of these, that you want to test.

 

NOT:
Reverses the value of its argument. Use NOT when you want to make sure a value is not equal to one particular value.
Syntax: NOT(logical)
- Logical is a value or expression that can be evaluated to TRUE or FALSE.
- If logical is FALSE, NOT returns TRUE; if logical is TRUE, NOT returns FALSE.

 

SUMPRODUCT:
Multiplies corresponding components in the given arrays, and returns the sum of those products.
Syntax: SUMPRODUCT(array1,array2,array3, ...)
- Array1, array2, array3, ...   are 2 to 255 arrays whose components you want to multiply and then add.
- The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.
- SUMPRODUCT treats array entries that are not numeric as if they were zeros.
 
INDIRECT:
Returns the reference specified by a text string. References are immediately evaluated to display their contents.
Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.
Syntax: INDIRECT(ref_text,a1)
- Ref_text   is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string.
- If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference.
- If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.
- If cell A3 mentions "B3" and cell B3 mentions "45":  INDIRECT($A$3)  ->  Value of the reference in cell A3 (45).
 
LOOKUP - VECTOR FORM
A vector is a range of only one row or one column. The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range.
Syntax: LOOKUP(lookup_value,lookup_vector,result_vector)
- Lookup_value: A value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.
- Lookup_vector: A range that contains only one row or one column. The values in lookup_vector can be text, numbers, or logical values.
- The values in lookup_vector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent.
- Result_vector: A range that contains only one row or column. It must be the same size as lookup_vector.
- If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value.
 
LEN:
LEN returns the number of characters in a text string.
Syntax: LEN(text)
- Text   is the text whose length you want to find. Spaces count as characters.
   

Bottom Ad

   
   
© 2014 GlobaliConnect.com. All rights reserved.