Get value of last non-empty cell

In this example, the goal is to get the last value in column B, even when data may contain empty cells. This is one of those puzzles that comes up frequently in Excel, because the answer is not obvious. One way to do it is with the LOOKUP function, which can handle array operations natively and always assumes an approximate match. This makes the formula surprisingly simple and compact:

=LOOKUP(2,1/(B:B<>""),B:B)

Working from the inside out, we use a logical expression to test for empty cells in column B:

B:B<>""

The range B:B is a full column reference to every cell in column B. The key advantage of a full column reference is that it remains unaffected when rows are deleted or added.

Note: Excel is generally smart about evaluating formulas that use full column references, but you should take care to manage the used range. In this case, because we create a "virtual column" of over 1 million rows with the expression B:B<>"", the used range likely doesn't matter as much, but the formula is definitely much slower than the same formula with fixed ranges. If you notice performance problems, try limiting the range. For example: =LOOKUP(2,1/(B1:B100<>""),B1:B100)

The logical operator <> means not equal to, and "" means empty string, so this expression means column B is not empty. The result is an array of TRUE and FALSE values, where TRUE represents cells that are not empty and FALSE represents cells that are empty. This array begins like this:

{FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE...}

Next, we divide the number 1 by the array. The math operation automatically coerces TRUE to 1 and FALSE to 0, so we have:

1/{0;1;0;1;1;1;1;1;1;1;1;1;1;1;0;...}

Since dividing by zero generates an error, the result is an array composed of 1s and #DIV/0 errors:

{#DIV/0!;1;#DIV/0!;1;1;1;1;1;1;1;1;1;1;1;#DIV/0!;...}

This array becomes the lookup_array argument in LOOKUP. Notice the 1s represent non-empty cells, and errors represent empty cells.

The lookup_value is given as the number 2. We are using 2 as a lookup value to force LOOKUP to scan to the end of the data. LOOKUP automatically ignore errors, so LOOKUP will scan through the 1s looking for a 2 that will never be found. When it reaches the end of the array, it will "step back" to the last 1, which corresponds to the last non-empty cell.

Finally, LOOKUP returns the corresponding value in result_vector (given as B:B), so the final result is: June 30, 2020.

The key to understanding this formula is to recognize that the lookup_value of 2 is deliberately larger than any values that will appear in the lookup_vector. When lookup_value can't be found, LOOKUP will match the next smallest value that is not an error: the last 1 in the array. This works because LOOKUP assumes that values in lookup_vector are sorted in ascending order and always performs an approximate match. When LOOKUP can't find a match, it will match the next smallest value.

Get corresponding value

You can easily adapt the lookup formula to return a corresponding value. For example, to get the price associated with the last value in column B, the formula in F7 is:

=LOOKUP(2,1/(B:B<>""),C:C) // get price

The only difference is that the result_vector argument has been supplied as C:C.

Dealing with errors

If the last non-empty cell contains an error, the error will be ignored. If you want to return an error that appears last in a range you can adjust the formula to use the ISBLANK and NOT functions like this:

=LOOKUP(2,1/(NOT(ISBLANK(B:B))),B:B)

Last numeric value

To get the last numeric value, you can add the ISNUMBER function like this:

=LOOKUP(2,1/(ISNUMBER(B:B)),B:B)

Position of the last value

To get the row number of the last value, you can use a formula like this: 

=LOOKUP(2,1/(B:B<>""),ROW(B:B))

We use the ROW function to feed row numbers for column B to LOOKUP as the result_vector to get the row number for the last match.