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.

Hyper_Search