## 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 you click the cell that contains the HYPERLINK function, Excel goes to that location in the table.

**Formula in cell B2:**

### Explaining formula in cell B2

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

MATCH(A2, Table1[Number], 0)

becomes

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

and returns 39.

*Step 2 - Create a cell reference as text*

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

becomes

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

becomes

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

becomes

ADDRESS(41, 4)

and returns $D$41

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

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

becomes

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

### What if there are multiple values matching your lookup value?

(Don´t ask me why the underline and link color disappeared in the hyperlink array formulas.)

**Array formula in cell B2:**

**How to create an array formula**

- Copy above array formula
- Select cell B2
- Click 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; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 261; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""}

**Step 2 - Return the k-th smallest value**

SMALL(*array*, *k*)

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

becomes

SMALL({""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 41; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 261; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""}, 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]), ""), COLUMN(A1)), 4), "Go to "&ADDRESS(SMALL(IF($A2=Table1[Number], ROW(Table1[Number]), ""), COLUMN(A1)), 4))

returns

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

### Download excel *.xlsx file

### Category: Hyperlinks

Create links to all sheets in a workbook

The macro demonstrated below creates hyperlinks to all sheets in current workbook. Select a start cell and then run macro […]Comments(30) Filed in category: Excel, Hyperlinks

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 […]Comments(19) Filed in category: Hyperlinks, Pivot table

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 […]Comments(5) Filed in category: Excel, Hyperlinks

Quickly create links to sheets, tables, pivot tables and named ranges in a workbook

A week ago I posted Create links to all sheets in a workbook and today I want to show you how […]Comments(3) Filed in category: Excel, Hyperlinks, Named range

### Category: Vlookup

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.Comments(445) Filed in category: Excel, VLOOKUP and return multiple values

Improve your VLOOKUP formula and return multiple values

Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no […]Comments(157) Filed in category: Excel, VLOOKUP and return multiple values

Comments(74) Filed in category: Excel, Vlookup

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]Comments(45) Filed in category: Excel, Unique distinct values, VLOOKUP and return multiple values

Vlookup with 2 or more lookup criteria and return multiple matches

VLOOKUP and return multiple matches based on many criteria.Comments(38) Filed in category: Excel, VLOOKUP and return multiple values

### 14 Responses to “Locate lookup values in a table [HYPERLINK]”

### Leave a Reply

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

<code>your formula</code>

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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

Oliver,

I am happy you like it!

Array formula in cell B2:

Download excel *.xlsx file

Find-values-quicklyv2.xlsx

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

Oliver,

Change the table references to absolute cell references.

Example:

to

Note! COUNTIF($A$2:A2, A2) uses both relative and absolute cell references.

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

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

RAJ.A.D,

read this post: Locate a particular cell in a table

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

Hello oscar

i wanted to use your formula and play a bit with it but the file doesnt seem to work when i download (error cant open specific file when i press a hyperlink) now i wanted to know if it is possible ot extend that the look up value is now compared with the row number can be searched in 3 diffrent rows? And also is it possible to do it over several sheets?

Hello,

How about if I am using open office?

Faith

Sorry, I don't know.

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,

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.

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