I'm trying to set up a formula to automatically calculate the % change between the most recently added cell in a range (which includes #N/A values at the bottom of the range) and the cell immediately above it. I've been using this formula to obtain the value of the bottom not #N/A cell:

```
LOOKUP(2, 1/NOT(ISNA(G8:G19)), G8:G19)
```

Which is working fine. My first thought on how to reach the cell above it was to use OFFSET, like so:

```
OFFSET(LOOKUP(2, 1/NOT(ISNA(G8:G19)), G8:G19), -1, 0)
```

but this gives me an error, I think because the lookup function is returning the value in the cell rather than the cell reference. How should I format a function to return the value of the cell above the last non-N/A cell in a range?

Try this alternative for seeking the last non-error, numerical value in column G.

```
=index(G:G, match(1e99, G:G))/index(G:G, match(1e99, G:G)-1)
```

Using MATCH to find the last number in a column returns the row number to INDEX. It is a simple matter to subtract 1 from a row number.

One method is to use this array formula:

```
=INDEX($G$8:$G$19,MATCH(2,IF(NOT(ISNA($G$8:$G$19)),1))-1)
```

Being an array formula it must be confirmed with Ctrl-Shift-Enter on exiting edit mode instead of enter. If done properly then Excel will put `{}`

around the formula.