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

*Article last 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

How to generate random numbers and text

What's on this page Random numbers with a decimal Random negative numbers with two decimals Random negative numbers with three […]

Locate lookup values in a table [HYPERLINK]

Today I´ll show you how to search a table column and jump to that table cell using the hyperlink function. When […]

How to quickly find the maximum or minimum value [Formula]

Question: I have three columns and how do I identify the largest and smallest number? Where is the value? I […]

Create links to all sheets in a workbook

The macro demonstrated below creates hyperlinks to all worksheets in the current worksheet. You will then be able to quickly […]

Follow hyperlinks in a pivot table

Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]

Locate lookup values in a table [HYPERLINK]

Today I´ll show you how to search a table column and jump to that table cell using the hyperlink function. When […]

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

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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:

Oscar, let's use CELL("filename") to make a filename-sheetname part dynamic, so, combined with David's suggestion, it will look like: HYPERLINK(REPLACE(CELL("filename"),1,FIND("[",CELL("filename")),"[")&"!B"&MAX(MATCH(0,B:B,-1),MATCH(CHAR(255),B:B,1))+1,"HYPERLINK")

Leonid,

great comment.

Thank you!