I have many columns all labeled with many many values underneath, which can be words or numbers

Here is the current equation `=INDEX(AK6:AK94,MODE(MATCH(AK6:AK94,AK6:AK94,0)))`

I have this on the in cell 5 of each column.

The number of values in each column may increase or decrease. If i reference the entire column (until the end of the worksheet) the blank spaces interfere with an accurate output.

How do I reference cell `A6 to Last Non-Blank`

# Best How To :

You need to determine the row of last non-blank cell in the column. The method for this would depend on whether there are blank cells in the middle, for instance.

Two alternatives are (taken from here*):

```
=SUMPRODUCT(MAX(($AK6:$AK94<>"")*(ROW(AK6:AK94))))
=INDEX(MAX(($AK6:$AK94<>"")*(ROW(AK6:AK94))),0)
```

Then you can use this value with `OFFSET`

to get a reference to the target cell. So your range will be (using the second form)

```
A6:OFFSET(AK1,INDEX(MAX(($AK6:$AK94<>"")*(ROW(AK6:AK94))),0)-1,0)
```

This expression will be embedded in a formula.

Notes:

- You may have to change absolute/relative references.
- Depending on the formula you embed the expression in, I foresee you might need to enter your formula as an array formula, with Ctrl+Shift+Enter.
- *This aims at getting the last non-blank value instead of a reference to the cell, but some of the results posted are useful.