Last row in mixed data with blanks

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.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar