Locate lookup values in an Excel table [HYPERLINK]

Author: Oscar Cronquist Article last updated on October 18, 2019

Today I'll show you a formula that returns a hyperlink pointing to a location based on a lookup value. When you press with left mouse button on the cell that contains the hyperlink text, Excel takes in an instant to that location in the table.

The formula is dynamic meaning if you change the search value the formula changes the location and the hyperlink text instantly.

Formula in cell C3:

=HYPERLINK("[Find values quickly.xlsx]Table!"&ADDRESS(MATCH(B3, Table1[Number], 0)+MIN(ROW(Table1[Number]))-1, 5), "Go to "&ADDRESS(MATCH(B3, Table1[Number], 0)+MIN(ROW(Table1[Number]))-1, 5))

This formula finds only the first instance of the search value, se further down the article for a formula that returns multiple results.

The Excel defined Table is located on worksheet "Table", the image above shows some records of that Excel defined Table.

Explaining formula in cell C3

I recommend you use the "Evaluate Formula" feature in Excel to examine calculations step by step.

Select cell C3, go to tab "Formulas" on the ribbon and press with left mouse button on the "Evaluate Formula" button.

(The formula shown in the above image is not the formula used in this article.)

Press with left mouse button on "Evaluate" button to see the next step in the formula calculations.

Step 1 - Return the relative position of an item in an array that matches a specified value

The MATCH function returns the relative position of a value in a column.

MATCH(A2, Table1[Number], 0)

becomes

MATCH(0.260463529541505, {0.129000449044708;0.440537695535749;0.532039509437168; ...}, 0)
(Array shortened)

and returns 39.

Step 2 - Create a cell reference as text

The ADDRESS function returns a cell reference based on a row and column number. ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

ADDRESS(MATCH(A2, Table1[Number], 0)+MIN(ROW(Table1[Number]))-1, 5)

becomes

ADDRESS(39+MIN(ROW(Table1[Number]))-1, 5)

The MATCH function returns a relative position, it does not take into account cells above the Excel Table. We need to determine how many cells are above the Excel Table.

ADDRESS(39+MIN(ROW(Table1[Number]))-1, 5)

becomes

ADDRESS(39+MIN(3)-1, 5)

becomes

ADDRESS(41, 5)

The second argument is the column number and is hardcoded into the formula, you must change this to the column number to a number representing the column you search.

ADDRESS(41, 5)

and returns $E$41

Step 3 - Create a shortcut to a cell in sheet Table

The HYPERLINK function lets you build a hyperlink in a cell. It has the following arguments: HYPERLINK(link_location, [friendly_name])

HYPERLINK("[Find values quickly.xlsx]Table!"&ADDRESS(MATCH(A2, Table1[Number], 0)+MIN(ROW(Table1[Number]))-1, 5), "Go to "&ADDRESS(MATCH(A2, Table1[Number], 0)+MIN(ROW(Table1[Number]))-1, 5))

becomes

=HYPERLINK("[Find values quickly.xlsx]Table!$D$41, "Go to $D$41")

The animated image above shows what happens when you press with left mouse button on a hyperlink, note that the lookup value match the number.

What if there are multiple values matching your lookup value?

Array formula in cell C3:

=IFERROR(HYPERLINK("[Find values quickly.xlsx]Table!"&ADDRESS(SMALL(IF($B3=Table1[Number], ROW(Table1[Number]), ""), COLUMNS($A$1:A1)), 5), "Go to "&ADDRESS(SMALL(IF($B3=Table1[Number], ROW(Table1[Number]), ""), COLUMNS($A$1:A1)), 5)), "")

How to create an array formula

Copy above array formula
Select cell B2
Press with left mouse button on in formula bar
Paste array formula
Press and hold Ctrl + Shift
Press Enter

How to copy array formula

Select cell B2
Copy cell (Ctrl + c)
Select cell range C2:E2
Paste (Ctrl + v)
Select cell range B2:E2
Copy (Ctrl + c)
Select cell range B3:E7
Paste (Ctrl + v)

Explaining formula in cell B2

Step 1 - Check if a condition is met and return corresponding row value if TRUE

IF($A2=Table1[Number], ROW(Table1[Number]), "")

becomes

IF(0.260463529541505={0.129000449044708;0.440537695535749;0.532039509437168; ...}, {3, 4, 5, ...}, "")

and returns

{""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 41; ... ; ""} (Array shortened)

Step 2 - Return the k-th smallest value

SMALL(array, k)

SMALL(IF($A2=Table1[Number], ROW(Table1[Number]), ""), COLUMNS($A$1:A1))

becomes

SMALL({""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 41; ... ""}, 1)

and returns 41.

Step 3 - Create a shortcut to a cell in sheet Table

HYPERLINK("[Find values quickly.xlsx]Table!"&ADDRESS(SMALL(IF($A2=Table1[Number], ROW(Table1[Number]), ""), COLUMNS($A$1:A1)), 4), "Go to "&ADDRESS(SMALL(IF($A2=Table1[Number], ROW(Table1[Number]), ""), COLUMNS($A$1:A1)), 4))

returns

HYPERLINK("[Find values quickly.xlsx]Table!$D$41, "Go to $D$41)

Get the Excel file

Find-values-quickly-1.xlsx

Hyperlinks category

Create links to all sheets in a workbook programmatically
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 […]

Navigate to first empty cell using a hyperlink formula
This article will demonstrate how to create a hyperlink that takes you to the first empty cell below data in […]

See all articles in 'hyperlinks' category

Functions in this article

HYPERLINK MATCH ROW IFERROR SMALL IF COLUMNS

More than 1300 Excel formulas

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

16 Responses to “Locate lookup values in an Excel table [HYPERLINK]”

Oliver says:

May 29, 2013 at 10:28 pm

Ah there we go, I had some issues commenting on this before.

Firstly, thank you for these excellent examples, very helpful and instructive.

I'm an accountant and the second part of this article (hyperlinking to multiple matches) would be fantastically useful to me but unfortunately I usually work in a column format, not horizontally as the example shows.

Would you mind showing me how I would need to change the formula to reflect multiple matches in a single column. So that if columns C,D,E were cleared and A2 was repeated in say A4 and A5 then A4 would be the next match and say "Go to D260" (currently C2 as it is showing the matches horizontally). As there would be no further matches then A5 would be blank and then the matches for A6 would then continue vertically.

Thank you very much for your time and I hope you don't mind showing me how to achieve this result.

Kind regards,

Oliver

Reply
- Oscar says:
  
  May 30, 2013 at 12:04 pm
  
  Oliver,
  
  I am happy you like it!
  
  Array formula in cell B2:
  
  =IFERROR(HYPERLINK("[Find values quickly.xlsx]Table!"&ADDRESS(SMALL(IF($A2=Table1[Number], ROW(Table1[Number]), ""), COLUMN(A1)), 4), "Go to "&ADDRESS(SMALL(IF($A2=Table1[Number], ROW(Table1[Number]), ""), COUNTIF($A$2:A2, A2)), 4)), "")
  
  Get the Excel *.xlsx file
  Find-values-quicklyv2.xlsx
- Oliver says:
  
  June 5, 2013 at 7:23 pm
  
  Hi Oscar,
  Thanks so very much for the reply. I had tried using countif but clearly I wasn't putting it in the right spot!
  Above you've explained very clearly what is happening with the rows etc and so I'm quite happy with this, unfortunately I am having trouble tailoring this for my own purpose which is to not reference a table(Table1[Number]) but rather a sheet (Sheet5!D:D). Somehow when I change the references, something unexpected happens and the formula no longer functions quite the same. Would you be so kind as to show me the correct usage for this method?
  So for instance if I had the same sort of numbers but in column D in say, Sheet5, not a table.
  My other question is whether there is a method for achieving the same result in VBA. While I have seen many attempts at creating separate lists showing differences or matches, this is quite unique in that it allows for navigation of a table (which is more reflective of what a manual process would be if I had to tick through each match).
  Thank you very much for your help,
  Oliver
- Oscar says:
  
  June 7, 2013 at 12:34 pm
  
  Oliver,
  
  Change the table references to absolute cell references.
  
  Example:
  
  =IFERROR(HYPERLINK("[Find values quickly.xlsx]Table!"&ADDRESS(SMALL(IF($A2=Table1[Number], ROW(Table1[Number]), ""), COLUMN(A1)), 4), "Go to "&ADDRESS(SMALL(IF($A2=Table1[Number], ROW(Table1[Number]), ""), COUNTIF($A$2:A2, A2)), 4)), "")
  
  to
  
  =IFERROR(HYPERLINK("[Find values quickly.xlsx]Table!"&ADDRESS(SMALL(IF($A2=$D$1:$D$100, ROW($D$1:$D$100), ""), COLUMN(A1)), 4), "Go to "&ADDRESS(SMALL(IF($A2=$D$1:$D$100, ROW($D$1:$D$100), ""), COUNTIF($A$2:A2, A2)), 4)), "")
  
  Note! COUNTIF($A$2:A2, A2) uses both relative and absolute cell references.
Oliver says:

May 30, 2013 at 9:32 pm

Hi Oscar,

Thanks so very much for the reply. I had tried using countif but clearly I wasn't putting it in the right spot!

Above you've explained very clearly what is happening with the rows etc and so I'm quite happy with this, unfortunately I am having trouble tailoring this for my own purpose which is to not reference a table(Table1[Number]) but rather a sheet (Sheet5!D:D). Somehow when I change the references, something unexpected happens and the formula no longer functions quite the same. Would you be so kind as to show me the correct usage for this method?

So for instance if I had the same sort of numbers but in column D in say, Sheet5, not a table.

My other question is whether there is a method for achieving the same result in VBA. While I have seen many attempts at creating separate lists showing differences or matches, this is quite unique in that it allows for navigation of a table (which is more reflective of what a manual process would be if I had to tick through each match).

Thank you very much for your help,

Oliver

Reply
RAJ.A.D says:

July 4, 2013 at 7:47 am

hai oscar,
I have a table with customers as column & products as rows. Price may different to each customer and product. i am using the following formula to find out the rate of an item for a particular customer;

"=IF(INDEX(TABLE3,MATCH(REF_ID,INDEX(TABLE3,,1),0),MATCH(REF_prod,INDEX(TABLE3,1,),0))<=0,"PLEASE INSERT A RATE",(INDEX(TABLE3,MATCH(REF_ID,INDEX(TABLE3,,1),0),MATCH(REF_prod,INDEX(TABLE3,1,),0)))).

now i want to locate/hyperlink the particular cell intersecting the customer reference and product reference. can u pls help me

Reply
- Oscar says:
  
  July 9, 2013 at 12:41 pm
  
  RAJ.A.D,
  
  read this post: Locate a particular cell in a table
Create a HYPERLINK in Microsoft Excel | My Spreadsheet Lab says:

August 30, 2015 at 8:12 pm

[…] out Oscar Cronquist’s post about dynamic […]

Reply
Jeroen says:

November 28, 2015 at 5:10 am

Hello oscar

I wanted to use your formula and play a bit with it but the file doesn't seem to work when I got (error cant open the specific file when I press a hyperlink) now I wanted to know if it is possible to extend that the lookup value is now compared with the row number can be searched in 3 different rows? And also is it possible to do it over several sheets?

Reply
Faith says:

February 8, 2017 at 5:36 pm

Hello,

How about if I am using open office?

Reply
- Oscar says:
  
  February 9, 2017 at 8:06 am
  
  Faith
  
  Sorry, I don't know.
esse says:

February 22, 2017 at 6:04 am

I have 18 columns and more than 40 rows data, and I want peak the largest value among and lookup the corespondent value (heading and field name) with the data,

I tray =index(array,match(lookup value, array, 0)) returns #N/A,

array assume from $C$4:$T$391
lookup value AH4,

Reply
- Oscar says:
  
  February 23, 2017 at 2:22 pm
  
  esse
  
  Can you post your formula, there seems to be nothing wrong with the one you provided.
  
  You get a #N/A error if the lookup value is not found.
Qadeer says:

March 19, 2017 at 7:24 am

Awesome!, Thanks, you made my day, really relieved.

Reply
Mark says:

April 14, 2020 at 7:37 am

I get the message " Cannot open the specified file"

Reply
- Oscar says:
  
  April 14, 2020 at 12:51 pm
  
  Mark,
  
  You need to enter the name of your workbook file in the formula:
  
  [Find values quickly.xlsx]Table!
  
  Find values quickly.xlsx is my file name.

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