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

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

Create a hyperlink linked to the result of a two-dimensional lookup

The image above shows a formula in cell C2 that searches for a value based on two conditions specified in [โฆ]

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 above creates hyperlinks to all worksheets in the current worksheet. You will then be able to quickly [โฆ]

Use 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 an Excel table [HYPERLINK]

Today I'll show you a formula that returns a hyperlink pointing to a location based on a lookup value. When [โฆ]

List all hyperlinks in worksheet

The macro in this blog post lists all hyperlinks in a worksheet. It also lists all words beginning with "http" [โฆ]

### 9 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

Paste image link to your comment.

**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!

What is wrong with the below formula? When I click on the hyperlink, the cursor stops at A2270. Tried out various options, yet cursor stops at A2270.

`=HYPERLINK("[BANK STATEMENT.xlsx]ICICI$c$"&MATCH(CHAR(255),C2:C3000,1)+1,"BACK")`

Help !

Thanks,

`Martin`

Martin,

Perhaps there is a CHAR(255) character in C2270?

This article demonstrates a formula that extracts ANSI characters:

https://www.get-digital-help.com/2018/01/26/identify-all-characters-in-a-cell-value/