Microsoft Office Tutorials and References

In Depth Information

The following array formula is similar to the previous formula, but it returns the last nonempty cell in a row (in

this case, row 1):

{=INDEX(1:1,MAX(COLUMN(1:1)*(1:1<>””)))}

To use this formula for a different row, change the 1:1 reference to correspond to the row.

Figure 15-11 shows an example for the last value in a column, and the last value in a row.

An alternative, non-array formula that returns the last nonempty non-error cell in a row is

=LOOKUP(2,1/(1:1<>””),1:1 )

Figure 15-11:
Using array formulas to return the last nonempty cell in a column or row.

Working with Multicell Array Formulas

The preceding chapter introduces array formulas that you can enter into multicell ranges. In this section, I

present a few more multicell array formulas. Most of these formulas return some or all of the values in a range,

but are rearranged in some way.