Here are two ways to find the last number in a column. Let’s see how Excel does it:
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Here are two examples of summary accounts receivables for three customers:
The individual receivable worksheets that they are pulling from are these:
The first method uses a combination of INDEX and COUNT functions. The syntax for INDEX is:
Our array is just the column of balances from each of the individual sheets. The row number will use the COUNT function to see how many items are in that column and use that number to indicate the row number. Since our array is only one column, we can either insert a “1” for the column number or leave it blank (note that in the syntax, [col_num] is in square brackets meaning that it is optional, and will default to “1” if nothing is indicated).
The second method uses the LOOKUP function. The syntax for LOOKUP is:
We are using 9.99E+307 as our LOOKUP value. This is the largest number (Big Number) that Excel can store. When you use that as the LOOKUP value, Excel won’t find it and just returns the last number it finds. Our array, as in the first example, is the list of balances for each account.
Either method is simple and can be used to return the last number in a column.