Within Excel there is not a single formula to get the last element within a column or a row, but it can be achieved by a combination of worksheet functions within a variety of differing requirements. The fact that this is achieved in Excel by combining a number of functions can be seen as a strength of Excel (a strong base set of functions that provide a large degree of extensibility), or a weakness (lack of commonly required functions), depending upon your viewpoint.
The following is an extract of a comprehensive White Paper Bob Phillips and I created together. If you want more details about getting the last value including alternative solutions, benchmarks, VBA coding, etc. you may take a look at it.
- Getting the last numeric value in a column:
- Formula: =INDEX(A:A,MATCH(9.99999999999999E307,A:A))
- The INDEX/MATCH formula searches for the value 9.99999999999999E307. This value is the highest value that can be represented in Excel. Therefore this formula returns the last numeric value that is smaller than or equal to this number.
- If no numeric entry exists within the range, the MATCH formula will return the #N/A error.
- Formula: =INDEX(A:A,MATCH(REPT(“Z”,255),A:A))
- The INDEX/MATCH formula uses a string consisting of 255 ‘Z’ characters to find the last text entry. For Excel, this string evaluates to the ‘largest’ string value.
- You cannot use the function REPT(CHAR(255),255), as the largest value. Whilst you might suspect that Excel evaluates this to the ‘largest’ string value, Excel evaluates the following formula:
- Formula: =LOOKUP(2,1/(1-ISBLANK(A1:A65535)),A1:A65535)
- This formula uses LOOKUP in its vector syntax form, with the lookup value as the first parameter, the lookup vector as second, and the result vector as the last parameter.
- The most interesting part of this formula is the lookup vector (the 2nd parameter). The formula element
in this example returns the following array
that is, the ISBLANK function returns an array of TRUE (blank cell) or FALSE (non-blank cell) values. Subtracting this from 1 converts the array to an array of 0 (blank) or 1 (non-blank) values. Dividing 1 by this array then returns an array of #DIV/0 (blank) or 1 (non-blank) values.
- The LOOKUP searches for the value ‘2’ within the array (which now consists only of ‘1’ and #DIV/0 values). The LOOKUP will not find this value, so it matches the last value that is less than or equal to lookup value. This is the last ‘1’ within the range which represents the last filled cell.
- Restriction: you can’t use a complete column reference such as A:A for this type of formula.
- This type of formula can be used for a lot of similar problems using the second parameter to create a lookup vector consisting of either ‘1’ or ‘#DIV/0’ errors by setting the Boolean expression accordingly. I personally saw this usage first in a posting from Aladin Akyurek.
All these formulas can easily adapted for searching in a row by changing the range reference. For examples see the above mentioned White Paper.