**The common formula for getting the last row**

`=MATCH(2,1/(range<>""))`

**Elaboration**

In Excel, the function used to obtain the last relative cell (the last column on the last row) on a spreadsheet with mixed data and blank cells are known as the MATCH function.

NB: MATCH is an array formula. When working with array formulas, simply click on the appropriate cell and enter the formula. Then press Ctrl+Shift+Enter. The formula will be inserted automatically into a pair of curly braces.

## Example

* Figure 1. Displays on cell D1 the last row of a worksheet with mixed data, obtained with MATCH function.*

We entered the formula:

**=MATCH(2,1/(C1:C7<>""))**

into column D1.

**Obtaining the last relative position on a spreadsheet**

When making use of more complex formulas in excel, the need sometimes arises to locate the last position of the data that a list contains. Sometimes, the result could be the last column containing data and sometimes it’s the last row. Other times, it’s the combination of both.

NB: The result will be the last relative position of data in the range specified and not the row number on the spreadsheet.

*Figure 2. Displays mixed data on an excel worksheet, and their last relative positions.*

**Understanding how this formula operates**

Here, the formula implements the MATCH function constructed specifically for locating the position of the last data-containing cell in the specified range.

Taking a close look at the inner workings, this is how the lookup array in the MATCH function is constructed:

= 1 / (C1:C7 <> “”))

= 1 /

{TRUE;FALSE;TRUE;FALSE;TRUE;TRUE

;FALSE }

=

{1; #DIV/0! ;1; #DIV/0! ;1;1; #DIV/0!

}

NB: the array contains only two values: 1 and #DIV/0!

In “appropriate match mode”, MATCH is equated to value 2 and the third argument is left out.

The process is set up in a way that MATCH always locates the last 1 in the specified array which matches the last data-containing cell. This is as a result of its failure to locate the lookup value of 2.

This method is applicable to all data types; text, numbers, dates and null text strings.

**Creating dynamic ranges**

This formula can also be used to create dynamic ranges. They can be created with different functions e.g. OFFSET.

## Leave a Comment