First And Last Elements In A List On A Worksheet

This page describes formulas you can use to retrieve the first and last elements in a list of data on a worksheet.

Introduction

Many, if not most, workbooks contain lists of data that are dynamic. That is, elements of the list are added or deleted, so you can't hard code the address of the last element in a formula. It needs to be calculated in the formula at run time. This page describes Excel worksheet formulas that you can use to get the first and last elements in a list of data elements, in addition to getting the positions of the first and last elements.

You can download the workbook with all the example code on this page.

The Formulas

We will present six formulas on this page: first and last elements in a row range and in a column range; row number of first and last elements in a row range and in a column range; and offsets within the list of the first and last elements in a row range and a column range.

NOTE: All of the formulas on this page are array formulas. To enter an array formula, type the formula in the cell and press CTRL SHIFT ENTER rather than just ENTER. You must do this when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula in the formula bar enclose in curly braces { }. The formulas will not work correctly if you do not array-enter the formula. See the Array Formulas page for a full discussion of array formulas.

First And Last In A Range In A Column

In these formulas, we assume the data is in a single column spanning several rows, in the range D9:D16. In all formulas, if the range has no members, an empty string is returned.

To get the first element in the range, use the following array formula:

=IF(COUNTA(D9:D16)=0,"",INDEX(D9:D16,MIN(IF(D9:D16="",99^99,ROW(D9:D16)))-ROW(D9:D16)+1,1))

To get the last element in the range, use the following array formula

=IF(COUNTA(D9:D16)=0,"",INDEX(D1:D16,MAX((D9:D16<>"")*(ROW(D9:D16))),0))

To get the row number of the first element in the range, use the following array formula:

=IF(COUNTA(D9:D16)=0,"",MIN(IF(D9:D16="",99^99,ROW(D9:D16))))

To get the row number of the last element in the range, use the following array formula:
=IF(COUNTA(D9:D16)=0,"",MAX((D9:D16<>"")*(ROW(D9:D16))))

To get the 0-based offset in the range for the first element, use the following array formula.

=IF(COUNTA(D9:D16)=0,"",MIN(IF(D9:D16="",99^99,ROW(D9:D16)))-ROW(D9:D16))

First And Last In A Range In A Row

The following formulas are used to get data out of a range that is in a single row, spanning several columns. In these examples, we will assume the array is in D21:K21.

To get the first element in the range, use the following array formula:

=IF(COUNTA(D21:K21)=0,"",INDEX(D21:K21,1,MIN(IF(D21:K21="",99^99,
COLUMN(D21:K21)))-COLUMN(D21:K21)+1))
This formula is split into two lines for readability. In practice, it would be all one line.

To get the last element in the range, use the following array formula:

=IF(COUNTA(D21:K21)=0,"",INDEX(D21:K21,1,MAX((D21:K21<>"")*
COLUMN(D21:K21))-COLUMN(D21:J21)+1))
This formula is split into two lines for readability. In practice, it would be all one line.

To get the column number of the first element in the range, use the following array formula:

=IF(COUNTA(D21:K21)=0,"",MIN(IF(D21:K21="",99^99,COLUMN(D21:K21))))

To get the column number of the last element in the range, use the following array formula:

=IF(COUNTA(D21:K21)=0,"",MAX((D21:K21<>"")*(COLUMN(D21:K21))))

To get the 0-based offset of the first element in the range, use the following array formula:

=IF(COUNTA(D21:K21)=0,"",MIN(IF(D21:K21="",99^99,COLUMN(D21:K21)))-
COLUMN(D21:K21))
This formula is split into two lines for readability. In practice, it would be all one line.

To get the 0-based offset of the last element in the range, use the following array formula:

=IF(COUNTA(D21:K21)=0,"",MAX((D21:K21<>"")*(COLUMN(D21:K21)))-
COLUMN(D21:K21))
This formula is split into two lines for readability. In practice, it would be all one line.

See also LastValueInRowOrColumn.aspx.

You can download the workbook with all the example code on this page.

This page last updated: 11-January-2013.