Excel Lookup Formula for Last Item in Column

How can you get the last number in an Excel column? I needed to do that in one of my sample files, so here’s the formula that I used. Then, someone asked me how to get the last item (text or number) from a column, so there’s a formula for that too.

Column with Numbers

I’m updating my Excel Weight Tracker files, which were created long before Excel introduced named tables.

Switching to tables has made it easier to show a summary on the dashboard. The old dynamic ranges aren’t needed now – we can just refer to table columns instead.

The WeightData table, shown below, has columns where you enter the date and your weight every week.

weekly dates and weights

There is also a Dashboard sheet in the workbook, and I want the latest weight to show on the Dashboard.

What formula would you use to show that latest weight on the Dashboard sheet? My formula is below, and you might use a different solution.

Get the Last Number

The new dates and weights are entered at the end of the table, so I used the LOOKUP function on the Dashboard, to get the last number from the Weight column.

=LOOKUP(9.99999999999999E+307, WeightData[Wt])

LOOKUP formula for last number

What’s That Number?

According to Excel’s specifications, that strange number, 9.99999999999999E+307, is the largest number that can be typed into an Excel cell.

The LOOKUP function won’t find that in the weight column (I hope!), so the formula returns the last number it finds in that column.

To see this formula, and all the others, get one of the Weight Tracker files – either the Pounds/Kilos version, or the Stone version.

Tip – Name That Number

Instead of putting that confusing number into the formula, you could define a name, using that value.

Then, put the “friendly” name into the formula, so you don’t scare your co-workers.

To create the name:

Copy the number from the formula bar
On Excel’s Formula tab, click Define Name
Type a name, e.g. XL_Max
Leave the scope as Workbook
Type a comment that explains what the number is
Right-click the Refers To box, and click Paste, then click OK

define a name

Use the Defined Name

After you define the name, use it in the formula.

Select the cell with the LOOKUP formula, and click in the Formula bar.
Select the long number, and replace it with the defined name, XL_Max
Press Enter, to update the formula

use name in formula

Read more about Excel Names on my Contextures site.

Column with Assorted Items

In the previous formula, the LOOKUP function get the last number from a column. But how would you write a formula to get the last text or number in a column, which has assorted items, including blank cells and errors?

The Sample Data

Here’s the sample data, and the result in cell D2 should be “yes”. That’s the last entry which either either a number or text.

What lookup formula would you use to get that result? My solution is below the screen shot.

LOOKUP last entry - text or number

With values (and blank cells) in cells B1:B6, I used this LOOKUP formula to get the last entry that’s text or a number:

=LOOKUP(2,1/(B1:B6<>””),B1:B6)

Check for Empty Strings

Here’s how the formula works.

First, the formula tests each entry to see if it’s “not equal to” an empty string (<>””).

The result of that test is TRUE (1) or FALSE (0) for each cell in the range.

LOOKUP TRUE or FALSE

Divide with the Result

Then, the number 1 is divided by each TRUE/FALSE.

TRUE is equal to 1, and 1/1=1
FALSE is equal to zero, and 1/0 results in a #DIV/0! error
Any cells that contain an error will also return an error in this calculation

LOOKUP divide 1 by result

Find the Lookup Value

The lookup value is 2, which won’t be found, because the highest number is 1.

Since it can’t find a 2, the formula finds the position of the last number 1, and returns the value (“yes”) at that location, in the lookup range.

Get the Sample File

To see the first LOOKUP formula, which returns the last number in a column, get my my Excel Weight Tracker files. There are versions for pounds/kilos, or for stone.

__________________