Let’s say you have a table of codes and every month there are a few you want to check out. You could use a VLOOKUP to extract all the details for each code, but let’s say you want to view the codes in the table.
We you could use a filter, but I found another way to go straight to the code you want using a hyperlink.
To be more specific, the HYPERLINK function.
I have written about HYPERLINK before – check out this blog post.
This time I will enter a code in a cell and then use a few functions to do all the work for me. Ultimately they will work together to create a link to the cell that contains the code.
This is a simplified example but it will work for larger tables. It will find the first instance of the code.
The image below is our simple table.
As you can see I have a formatted table named tblCode. I use tbl as a prefix for all my formatted tables. This has advantages I will discuss in a future blog post.
I used Ctrl + T to create the formatted table and then renamed it at the top left in the Design ribbon tab.
The image below is our Search sheet where we create the hyperlinks.
I enter a code in the yellow cells in column A and click the word Link in column B and magically I am teleported to the correct cell on the Data 1 sheet.
I can even press F5 and then press Enter to return to the Search sheet just like a normal hyperlink.
The Magic
The magic happens in the Helper column – column C. The formula in that column creates the exact reference required by the HYPERLINK function.
I have mentioned in a previous blog post that using the HYPERLINK function can be tricky as it requires exact names and addresses for the hyperlink to work.
The formula in cell C2 which has been copied down is
="#"&CELL("address",INDEX(tblCode[Code],MATCH(A2,tblCode[Code],0)))
The # symbol at the starts is how you cope with spaces in hyperlinks.
The CELL function is an information function – in this case it returns a text string that describes the cell where the code can be found – perfect for our requirements.
We reference that cell in the CELL function using the good ‘ol INDEX-MATCH combination.
INDEX(tblCode[Code],MATCH(A2,tblCode[Code],0))
This demonstrates the power of the INDEX function. INDEX can refer to a cell rather than return a value. VLOOKUP for example can only ever return a value from a cell.
The MATCH function returns a number that refers to the position of the code being searched for within the table reference.
The INDEX uses the MATCH result to specify the cell, in this case cell A4.
If you used the above INDEX function by itself in a cell, it would return the code ABCD1236, much like the following VLOOKUP.
=VLOOKUP(A2,tblCode,1,0)
But when you use the INDEX with a function that requires a cell reference (like the CELL function) the INDEX will return the cell reference rather than the value in the cell, in this case the cell reference is Data 1!A4.
The formula in cell B2 which has been copied down is straightforward. The formula is
=IFERROR(HYPERLINK(C2,"Link"),"")
The IFERROR stops an error message displaying in column B when there is an error in column C. The error is column C is caused when either there is nothing in column A or the code in column A isn’t in the list in the Data 1 sheet. I have left that error in column C and would probably hide column C in a working model.
As you can see the HYPERLINK function itself is very simple
HYPERLINK(C2,"Link")
Cell C2 did all the work.
You could get rid of the Helper cell and create one long formula in cell B2 like
=IFERROR(HYPERLINK("#"&CELL("address",INDEX(tblCode[Code],MATCH(A2,tblCode[Code],0))),"Link"),"")
The file I used can be downloaded at the link below.
Please, how can this be edited to link to a sharepoint excel [browser excel] cell location? my link works when i have to excel file open in the desktop application from the synced onedrive location, but i need it to work when other users are viewing the spreadsheet in their web browser.
=HYPERLINK(“#”&CELL(“address”,INDEX(A_JobNum,MATCH(C5,A_JobNum,0))),”Jump to the data cell”)
I have searched extensively on the web with no solution.
Thank you!
Sorry I have limited experience with Sharepoint – my limited experience has shown me that SharePoint is a pain.
Hi,
I’m wondering if you can help me with the following problem. First of all, I’m not a English native speaker, so sometimes it is hard to understand for me.
I have a Excel workbook wherein is want to do the following thing. On tab 1 I have cells with data in text. In tab 3 I have some cells with the same data or sort of. I want to make a cross link. Sometimes the data is exact equal to each other, sometimes on tab 1 is the word Apple and in tab the the sentence is An Apple is growing on the tree. I want to make a hyperlink between these to cells. I know I can do that manually, but if I insert cells, cut and paste cells to other positions the hyperlink is incorrect. So I want to build a hyperlink which is able to find my searching word in another tab. The data could isn’t in one column per tab. I tried to make this myself with the help of your file, but I found that your file has only data in 1 column.
I hope you can help me!
Hi Patrick
Sorry I don’t know a way to look for something across multiple columns.
You can find a word amongst words in a cell using a MATCH as below
MATCH(“*”&A2&”*”,Sheet2!A:A,0)
The * is a wildcard character.
But you still need to know the column to look in for this work.
Sorry.
Neale
This is amazing! I struggled for weeks with this, and your solution fixed my problem instantly. Thank you!
Hi Bettina
Glad it worked.
Regards
Neale
Hi Neale,
Even though this works, the hyperlink friendly name does not show the text of the search result. For example, when I type “how” in the search box, it returns results with the word “how” in the results and a hyperlink to the correct instance, but the friendly name is missing. Can you assist me?
Here’s my formula, =IFERROR(HYPERLINK(“#”&CELL(“address”,INDEX(data,MATCH(C7,data,0))),””),””)
Unfortunately, I can’t upload the file or a portion of it due to copyright issues.
Regards,
Bettina
Hi Bettina
Try this
=IFERROR(HYPERLINK(“#”&CELL(“address”,INDEX(Data,MATCH(C7,Data,0))),C7),””)
The C7 also goes at the end of the HYPERLINK to be the label of the link.
Hope that helps.
Neale
hello and firstly ty been searching for days for how to do this the way i was looking for and you shown it purfectly except one thing. I would like to to show all instances in the column not just the first instance of what ever entered into the search.
this is the fomula that i configured and i am using the “helper” column for links. if the long fomula works better that would work either way.
=”#”&CELL(“address”, INDEX(Table3[PART NUMBER], MATCH(L4, Table3[PART NUMBER], 0)))
i have parts list with alot of parts numbers and some will be same. if i put them into stock i want to be able to go in and fond all those instances so i can jump to them and see who they were for.
Hi Jason
Have you considered using the FILTER function? It can list all the instances based on a criteria without having to go to the master list.
FILTER requires the subscription version.
Regards
Neale
Will i be able to do same as you example. search in say L4 for a part number 13771 and it display all the findings with hyperlinks to be able to click them one at a time to jump to each cell each originated from?
You can see all the findings listed, but not with hyperlinks back to the source.
If there is a unique entry/code then you could use that to get to the individual rows.