Last Item In Row Or Column

This page describes formulas to find the last non-blank element in a row or column.
ShortFadeBar

You can use array formulas to find the last non-blank cell in a column or row. With that address, you can use the INDIRECT function to get the value in that cell.

The following formula returns the row number of the last non-blank cell in the range B1:B100.

This works by creating two arrays, multiplying them together to form a third array and getting the maximum value of that array. The first array, (B1:B100<>"") contains TRUE or FALSE values, each of which is the result of comparing the cells in B1:B100 to an empty string. If a cell is empty, the result for that row is FALSE. If the cell is not blank, the result for that row is TRUE. The second array, ROW(B1:B100), is just the row numbers of the range B1:B100. When these two arrays are multiplied, (B1:B100<>"")*(ROW(B1:B100)), the result is another array whose elements are the product of the corresponding elements in the arrays being multiplied. During the multiplication, the TRUE values in the first array are converted to the number 1 and the FALSE values are converted to 0. Therefore, if a cell has a value, that cell's row number appears in the third array. If the cell is empty, a 0 appears in that array. The MAX function just returns the largest value in the third array, which is the maximum row number.

With that row number, you can use the ADDRESS function to get the cell's address as a text string:

Finally, you can use the INDIRECT function to the turn the text address into an actual range to get the value of the cell.

The following formula works in the same manner as the formula above, creating and multiplying arrays. The difference, of course, is that the formula tests columns, not rows. You can get the column number of the last non-blank column with the following formula. This is written for row 3. Adjust the references to row 3 to your actual row number.

This returns the right-most column that is not blank. To get the address of the cell, we use the ADDRESS function as before:

Finally, we use INDIRECT to turn the address string into a real Excel reference and get the value of the cell:

Remember, these are array formulas, so you must enter them by typing CTRL SHIFT ENTER rather than just ENTER. The formulas will not work correctly if you do not use CTRL SHIFT ENTER. You can learn much more about array formulas, how they work and how to use them, on the Array Formulas page.