## Quickly jump to last row in a data set using excel hyperlink function

*Article updated on January 22, 2018*

Today I´ll show you how to create a useful hyperlink in excel. If you click the link it will take you to the first empty row in a data set.

**Formula in cell C2:**

### Explaining the formula in cell C2

**Step 1 - Find the last nonempty cell in cell range B1:B100**

MATCH(CHAR(255),B1:B100,1)

becomes

MATCH(ÿ,B1:B100,1)

and returns 8. If your data set is bigger than row 100, expand cell range B1:B100 further down.

**Step 2 - Join file name, sheet and cell address**

"[Quickly jump to last row using hyperlinks.xlsx]Sheet1!$B$"&MATCH(CHAR(255),B1:B100,1)+1

becomes

"[Quickly jump to last row using hyperlinks.xlsx]Sheet1!$B$"&8+1

becomes

[Quickly jump to last row using hyperlinks.xlsx]Sheet1!$B$9

**Step 3 - Create the hyperlink**

HYPERLINK("[Quickly jump to last row using hyperlinks.xlsx]Sheet1!$B$"&MATCH(CHAR(255),B1:B100,1)+1,"Hyperlink")

becomes

=HYPERLINK([Quickly jump to last row using hyperlinks.xlsx]Sheet1!$B$9,"Hyperlink")

You can also use this keyboard shorcut to find the next empty cell: Ctrl + Arrow

### Download excel *.xlsx file

Quickly jump to last row using hyperlinks.xlsx

### Functions in this post

**MATCH(***lookup_value*, *lookup_array*, *[match_type]*)

Returns the relative position of an item in an array that matches a specified value in a specific order

### 5 Responses to “Quickly jump to last row in a data set using excel hyperlink function”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Oscar, I played around with this type of MATCH formula long ago and found it to be a little buggy. Noenetheless, I have found that the most general form of this formula is:

=MAX(MATCH(0,A:A,-1),MATCH(CHAR(255),A:A,1))

David Hager,

You are right, I remember sam´s comment now:

https://www.get-digital-help.com/2011/04/28/create-a-dynamic-named-range-in-excel/#comment-24493

I am at a complete loss with this. This hyperlink function should work perfectly but it completely ignores the array that I program it to look within and returns me to a cell that is outside of the lookup array.

=HYPERLINK("[CompletePull.xlsx]'Sheet2'!$E$"&MATCH(410000,Sheet2!E3:E1002,1)+1,1)

I am attempting to match the entered date and return the cursor to the empty cell below on sheet two. My cursor returns to cell C1 instead. Would anyone tell me where I am going wrong here? My lookup array is clearly Sheet2 E3:E1002. Why would it even return my cursor to anything outside of that range?

=HYPERLINK("[CompletePull.xlsx]'Sheet2'!$E$"&MATCH(410000,Sheet2!E3:E1002,1)+3,1)

Okay, I have figured out how to get this syntax to work for dates. For some reason I had to tell the match formula to return the reference plus 3. I am having a hard time dissecting that but if anyone can help me with it, it would be much appreciated.

Thank you Oscar, I've been trying to figure this one out for a long time.

Randy,

MATCH(410000,Sheet2!E3:E1002,1)

Is your list sorted in an ascending order?

Read this: