Part II: Working with Formulas and Functions
The INDEX function when used on a single column needs only a row argument. The third
argument indicates the column isn’t necessary. COUNTA is used to count the nonblank cells
in column B. That count is increased by 1 because we have a blank cell in the first row. The
INDEX function returns the 12th row of column B.
COUNTA counts numbers, text, dates, and anything except blanks. If there are blank rows in your data, COUNTA will
not return the desired result.
Finding the last number using LOOKUP
INDEX and COUNTA are great for finding values when there are no blank cells in the
range. If you have blanks and the values you’re searching for that are numbers, you can
use LOOKUP and a really large number. The formula in cell G5 of Figure 14.14 uses this
technique.
=LOOKUP(9.99E+307,D:D)
The lookup value is the largest number that Excel can handle (just under 1 with 308 zeros
behind it). Since LOOKUP won’t find a value that large, it stops at the last value it does find,
and that’s the value returned.
A number like 9.99E+307 is written in exponential notation. The number before the E has one number to the left of
the decimal and two to the right. The number after the E is how many places are required to move the decimal point
to show the number in regular notation (307 in this case). A positive number means to move the decimal to the right,
and a negative number means to move it to the left. A number like 4.32E-02 is equivalent to 0.0432.
This LOOKUP method has the additional advantage of returning the last number, even if
there are text, blanks, or errors in the range.
