## Explanation

This formula uses the MATCH function configured to find the position of the last non-empty cell in a range.

Working from the inside out, the lookup array inside MATCH is constructed like this:

```
=1/(B4:B10<>""))
=1/{TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE}
={1;#DIV/0!;1;#DIV/0!;1;1;#DIV/0!}
```

Note: all values in the array are either 1 or the #DIV/0! error.

MATCH is then set to match the value 2 in "approximate match mode", by omitting the 3rd argument is omitted.

Because the lookup value of 2 will never be found, MATCH will always find the last 1 in the lookup array, which corresponds to the last non-empty cell.

This approach will work with any kind of data, including numbers, text, dates, etc. It also works with null text strings that are returned by formulas like this:

```
=IF(A1<100,"")
```

### Dynamic range

You can use this formula to create a dynamic range with other functions like INDEX and OFFSET. See links below for examples and explanation:

*Inspiration for this article came from Mike Girvin's excellent book Control + Shift + Enter, where Mike does a great job explaining the concept of "last relative position".*