Daily Dose of Excel

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.

1. 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.
2. Getting the last text value in a column:
• 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:
  =REPT(“Z”,255)<rept (CHAR(255),255)
  to FALSE
3. Getting the last value of any type in a column:



• 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
  1/(1-ISBLANK(A1:A65535))
  in this example returns the following array
  {1;1;1;1;1;1;1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;…;#DIV/0!}
  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.

As Stephen I also like to thank Dick for opening his blog to others. So I’ll take the chance to cover some medium to advanced formula issues in my postings.
Excel provides a very powerful function – INDIRECT. It just lacks the functionality to access closed workbooks. The following is a compilation of common alternative solutions presented in the Excel newsgroups:

1. Laurent Longre has developed the free add-in MOREFUNC.XLL which includes the function INDIRECT.EXT
• You can use it in the same way as INDIRECT. e.g.:
  <span class="text">=INDIRECT.EXT("'C:   emp[book1.xls]sheet!'A1")</span>
• Note that in this formula you also specify the path/directory information
• If you use INDIRECT.EXT with an open workbook it behaves the same way as INDIRECT does

There’re some limitations to this function:

• It does not work reliable on all computers. So you have to try it in your individual environment.
• INDIRECT.EXT can only return a single cell reference from a closed workbook. So you can’t use it for example as second parameter in a VLOOKUP function:
  <span class="text">=VLOOKUP("search_text",INDIRECT.EXT("'C: emp[book1.xls]sheet!'A1:B20"),2,0)</span>

  won’t work.

• INDIRECT.EXT does not work with defined names within closed workbooks.
• If you have to access several closed workbooks your spreadsheet can become slow while re-calculating.
2. Use SQL.REQUEST:
• The usage is described here.
• This function is relatively slow and the data has to be organized in a database like structure (that is a single area with field names as top row).
• Note: Microsoft does not support this addin anymore.
3. Use Harlan Grove’s PULL function:
• Code can be found here.
• The function creates separate Excel application instances to “pull” data from closed workbooks.
• This function is more robust, can deal with non-database like layouts and can also deal with cell ranges.
• Example usage:
  <span class="text">=VLOOKUP("search_text",PULL("'C: emp[book1.xls]sheet!'A1:B20"),2,0)</span>

All these solutions have one common drawback: They’re quite slow. So use them carefully and don’t use them with large cell ranges.