Skills level: Advanced
The need to obtain the last value in a column of values is quite common. Think about a simple statement where you have a running total in the last column, and as a summary, you want to show the balance at the bottom. Every time that a new line or entry is entered, you want the balance at the bottom to adjust to show the last running total.
There are a few ways in which you can do this.
LOOKUP FUNCTION – Option 1
Use the LOOKUP function to look up a number larger than any number in your list. The LOOKUP function makes use of approximate match, in other words, if the value that you want to look up is 300, the lookup function will return the next largest value that is less than 300 (given that the list of values is sorted). However, if the value that you want to look up is larger than any of the numbers in your list, Excel will return the last value, even if your list is NOT sorted (therefore, it is not a problem that your running total to the right is not a sorted list).
For example, the balances in the above example do not ever exceed R3 000. Therefore, you can lookup the value of R3 000, and because it is larger than any one of the running totals, Excel will return the last value.
Syntax and arguments
=LOOKUP(lookup_value, lookup_vector, [result_vector])
Lookup_value: The value that the function searches for in a given list of values.
Lookup_vector: This is the list, or range of values, where the value above is searched for.
[Result_vector]: Optional argument. In this case, the result we want returned and the list where we are searching is the same range. For this reason, we do not need this argument. If the result we want to return is in another column, this argument is needed to point to that range.
The formula will look as follows:
For this client, however, it was easy to determine with a quick glance, that the running total did not exceed R3 000, and we knew that R 3000 was a suitable lookup value. What if you have thousands of clients, with balances ranging from anything between R100 to R10m, or even R100 million? How can you be certain that you select a lookup value large enough to return the LAST balance. If your lookup value is not large enough it might not return the correct value.
If you have to select the largest possible value, what would that be?
In Excel’s Calculation specifications and limits, the largest allowed positive number is given as 9.99999999999999E+307. That is the largest number that Excel can deal with.
A complete list can be found here:
Although this number is astronomical, and it is unlikely that you will deal with numbers close to being this big, it can be very useful to use in our LOOKUP formula. This way we don’t have to evaluate in each instance what value will be suitably large to use as the lookup value. Just use the largest one there is! Every time!
Therefore, let’s adjust our formula to use this number as the lookup value instead of 3000 (I am just going to use a few less 9’s, it will still be suitably large).
For those who are curious, and not a math geek, the “E+307” at the end of 9.99 means 10 to the power of 307. The expression therefore means 9.99 times 10 to the power of 307. Which equals a HUGE number.
Now you can use this formula in any list with any range of values.
(Note: This method is not suitable for text lookup values.)
LOOKUP FUNCTION – Option 2
Another way to use the lookup function is by creating a range of TRUE and FALSE values (which is recognised by Excel as the value 1 and zero respectively), then strip out the false values by dividing 1 by each true/false value (1/1 will return 1 for TRUE values and 1/0 will return a #DIV/0! error for FALSE values), so that the value corresponding to the last value of 1, is returned.
This is a mouthful, so let’s dissect the above one step at a time.
The formula (applied to the same example in the first option) would be
Create a range of true and false values - LOOKUP(2,1/(D5:D21<>""),D5:D21)
D5:D21 The list of values
<> Does not equal to
“” An empty cell
This part of the formula returns a TRUE value if a cell is not empty, and FALSE if it is. Because you have highlighted a range of figures (D5:D21), the result will also be a range of TRUE and FALSE values.
In our example, all the running totals in our range will be TRUE (because they do not equal an empty cell), except for the last 2 (D20 and D21) which are obviously empty.
The below illustration might help to understand what we are doing.
Convert the range of TRUE and FALSE values into a range containing only 1’s and errors. To do so, divide 1 by the above range – LOOKUP(2,1/(D5:D21<>""),D5:D21)
By dividing 1 by each of the TRUE/FALSE values, you have a new range containing 1 and #DIV/0 errors.
So instead of evaluating the actual list of values, you have created a new list consisting of only 1 or an error. Excel will ignore the errors, so it basically evaluates a list of only ones.
Determine a suitable lookup value - LOOKUP(2,1/(D5:D21<>""),D5:D21)
So which value is bigger than 1? Two should do, don’t you think?
The range that you have created in step 2, will never exceed a value of 1. So a value of 2 should be sufficient to ensure that the lookup value is always larger than the values in the range.
Determine the result vector - LOOKUP(2,1/(D5:D21<>""),D5:D21)
Unlike the first example, we need the optional [result_vector] argument here. Remember that if your lookup list and result list is the same list, then you don’t have to specify the result list. In this case however, our lookup list is now a list of 1’s and divide by zero errors (which Excel ignores). It will therefore return a one which is not very useful. Instead we specify the result vector as being the last column (D5:D21), so the value in this range that relates to the last value of 1 in the lookup list should be returned.
In short, Excel will look for a 2 value in a list of 1’s (ignoring the error values), which it will never find, and instead it will return the value in the result list that relates to the last “1” in the lookup list.
This formula can also be used for text values. For example, suppose that you want to include the last transaction in your statement summary at the bottom. Simply use the same method as before, but use the “Transaction” column as your range as follows:
Your result will be the last recorded transaction description in the Transaction column.
What if there are incomplete rows?
Empty cells: If there are empty cells in your column, Excel will still return the last value in the list.
Zero values: If you have zeros at the end of your list, remember that Excel will recognise it as a value, And therefore, if zero is the last value, this is what will be returned.
For example, if you fill the spaces in the last column to the bottom with zeros (just above the Balance Due), then both the formulae discussed above will return a zero value, instead of the last balance.
The good news is, you can adjust your formula slightly to account for this, using the same idea in option 2 before – creating a range of true and false values that you convert to a list of ones and errors.
Instead of creating a range of values not equal to an empty cell, create a range of values not equal to zero, like illustrated below. The empty cell (“”) part of the formula is simply replaced with a zero (0).
Problem solved! The result is the latest running total, even if it is followed by a couple of zeros.
Hi, and welcome to my blog :) I am an Excel enthusiast and want to help others keen on improving their own Excel skills. I hope you learn something useful on my blog.