Explanation

This formula uses theMATCH functioninapproximate match modeto locate the last text value in a range.

To do that, we use theREPT functionto repeat the letter “z” 255 times.

The number 255 represents the largest number of characters that MATCH allows in a lookup value.

Illustration of last relative position (last row, last column)

MATCH supports approximate and exact matching, andwildcards(* ?)

For example, =REPT(“x”,5) returns “xxxxx”.

Illustration of last relative position (last row, last column)

Illustration of last relative position (last row, last column)

Illustration of last relative position (last row, last column)

Illustration of last relative position (last row, last column)

Excel formula: Last row in mixed data with no blanks

Excel formula: Last row in mixed data with blanks

Excel formula: Last row in numeric data

Excel formula: Dynamic named range with OFFSET

Excel formula: Dynamic named range with INDEX

Excel MATCH function

Excel REPT function