Microsoft Excel Functions & Formulas

Use the INDEX, MAX, ISNUMBER, and COLUMN

functions to find the last number in a row

In this example, the last value in each row has to be determined

and copied to another cell. To do this, combine the INDEX, MAX,

ISNUMBER, and COLUMN functions in an array formula.

4

To determine the last number in a row:

1. Generate a table like that shown in Figure 11-3 using the

range A1:F6.

2. In cells A9:A13 enter numbers from 2 to 6.

3. Select cell B9 and type the following array formula:

=INDEX(2:2,MAX(ISNUMBER(2:2)*COLUMN(2:2))).

4. Press <Ctrl+Shift+Enter>.

5. Select cells B9:B13.

6. Select Fill and then Down from the Edit menu to retrieve

the last value in each of ...