## 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 column A and B. The image below shows an Excel Table containing values.

The formula returns a value based on a condition that matches a particular header name (horizontal lookup) and another condition that matches a value in the first column (vertical lookup), see image below.

For example, the formula in cell C2 in the image above looks for Product7 in column1 and Customer1 in Table headers and returns the intersecting value, in this case, 41 from the Excel Table. This is called a two-dimensional lookup, read this if you want to learn more: How to perform a two-dimensional lookup

The formula also creates automatically a hyperlink pointing to that particular value in the Excel Table, that is why you see values formatted as hyperlinks in column C.

This makes it really easy to find the value in the Excel Table, simply press with left mouse button on the hyperlink and you will be instantly taken to the correct value in the Excel Table.

I have a table with customers as columns & 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;

Now I want to locate/hyperlink the particular cell intersecting the customer reference and product reference.

The formula in column C searches for the product and the customer in the table below and returns the intersecting value.

Press with mouse on a value in column C and you will instantly select the returned value in the Excel Table.

Product6 / Customer5 does not have a value in the table so the formula tells you to insert a value here, see row 7 above.

Array formula in cell C2, sheet1:

You don't need to enter this formula as an array formula yf you are an Excel 365 subscriber, simply press Enter.

#### How to enter an array formula

- Select cell C2
- Copy (Ctrl + c) and paste (Ctrl + v) above array formula to Excel
- Press and hold CTRL + SHIFT simultaneously.
- Press Enter.
- Release all keys.

If you did it right, the formula begins and ends with curly brackets, like this:

{=*array_formula*}

Do not enter these characters yourself, they appear automatically.

### Explaining array formula in cell C2

Excel provides a tool that can help you examine, evaluate and troubleshoot a formula, press with left mouse button on the cell that contains the formula you want to check. In this case, we are going to evaluate cell C2.

Cell C2 contains a formula that creates a hyperlink which prevents us from selecting it, if you press with left mouse button on it you will be navigated to another cell in the Excel Table. We need to use the arrow keys to select cell C2 or press with right mouse button on with the mouse to select cell C2.

Go to tab "Formulas" on the ribbon. Press with mouse on "Evaluate Formula" button to open the "Evaluate Formula" dialog box. The "Evaluate" button lets you see the calculations step by step, then press with left mouse button on "Close" button to dismiss the dialog box.

#### Step 1 - Find row number of the matching product value

The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.

MATCH(*lookup_value*, *lookup_array*, [*match_type*])

The third argument in the function is 0 (zero), this means that it will perform an exact match. Table1[Column1] is a structured reference, in other words, a reference to a column named "Column1" in an Excel Table named Table1.

MATCH(Sheet1!A2, Table1[Column1], 0)

becomes

MATCH("Product7",{"Product1"; "Product2"; "Product3"; "Product4"; "Product5"; "Product6"; "Product7"; "Product8"; "Product9"; "Product10"}, 0)

and returns 7. "Poduct7" is the seventh value in the array counting from left to right.

#### Step 2 - Find column number of the matching customer value

This part of the formula calculates a number representing the column in the Excel Table that we want to extract a value from.

MATCH(Sheet1!B2, Table1[#Headers], 0)

becomes

MATCH("Customer1", {"Column1", "Customer1", "Customer2", "Customer3", "Customer4", "Customer5"}, 0)

and returns 2. "Customer1" is the second value in the array.

#### Step 3 - Return intersecting value

The INDEX function returns a value based on the row and column number calculated in the two previous steps.

INDEX(Table1, MATCH(Sheet1!A2, Table1[Column1], 0), MATCH(Sheet1!B2, Table1[#Headers], 0))

becomes

INDEX(Table1, 7, 2)

and returns 41. The image below shows the intersecting value based on the relative row and column number. "Relative" meaning rows and columns in the Excel Table, not in the worksheet.

#### Step 4 - Check if value is 0 (zero) and return intersecting value or "Insert rate"

The IF function returns text string "Insert rate here" if the formula returns a blank (nothing) and the value if it returns anything else.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

IF(INDEX(Table1, MATCH(Sheet1!A2, Table1[Column1], 0), MATCH(Sheet1!B2, Table1[#Headers], 0))=0, "Insert rate here", INDEX(Table1, MATCH(Sheet1!A2, Table1[Column1], 0), MATCH(Sheet1!B2, Table1[#Headers], 0)))

becomes

IF(41=0, "Insert rate here", 41)

becomes

IF(FALSE, "Insert rate here", 41)

and returns 41.

#### Step 5 - Calculate address to intersecting value

The ADDRESS function calculates the cell reference needed to build the hyperlink.

ADDRESS(*row_num*, *column_num*, [*abs_num*], [*a1*], [*sheet_text*])

"[Locate a particular cell in a table.xlsx]Sheet2!"&ADDRESS(MATCH(Sheet1!A2, Table1[[#All], [Column1]], 0), MATCH(Sheet1!B2, Table1[#Headers], 0))

becomes

"[Locate a particular cell in a table.xlsx]Sheet2!"&ADDRESS(8, 2)

becomes

"[Locate a particular cell in a table.xlsx]Sheet2!"&$B$8

and returns [Locate a particular cell in a table.xlsx]Sheet2!$B$8

#### Step 6 - Create hyperlink

The HYPERLINK function creates a hyperlink using the value [*friendly_name*] and the cell reference (*link_location)*.

HYPERLINK(*link_location*, [*friendly_name*])

HYPERLINK("[Locate a particular cell in a table.xlsx]Sheet2!"&ADDRESS(MATCH(Sheet1!A2, Table1[[#All], [Column1]], 0), MATCH(Sheet1!B2, Table1[#Headers], 0)), IF(INDEX(Table1, MATCH(Sheet1!A2, Table1[Column1], 0), MATCH(Sheet1!B2, Table1[#Headers], 0))=0, "Insert rate here", INDEX(Table1, MATCH(Sheet1!A2, Table1[Column1], 0), MATCH(Sheet1!B2, Table1[#Headers], 0))))

becomes

HYPERLINK([Locate a particular cell in a table.xlsx]Sheet2!$B$8, 41)

and returns 41 (hyperlink) in cell C2.

### 7 Responses to “Create a hyperlink linked to the result of a two-dimensional lookup”

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

Says "Cannot open specified file"... Am I doing something wrong?

chrisham,

Make sure you saved the file as "Locate a particular cell in a table.xlsx".

or change the array formula to

=HYPERLINK("[

yourfilename]Sheet2!"&ADDRESS(MATCH(Sheet1!A2, Table1[[#All], [Column1]], 0), MATCH(Sheet1!B2, Table1[#Headers], 0)), IF(INDEX(Table1, MATCH(Sheet1!A2, Table1[Column1], 0), MATCH(Sheet1!B2, Table1[#Headers], 0))=0, "Insert rate here", INDEX(Table1, MATCH(Sheet1!A2, Table1[Column1], 0), MATCH(Sheet1!B2, Table1[#Headers], 0))))Oscar -

Can you change the formula so that the hyperlink address will return the correct value if the source table does not start at A1? For example, my lookup range starts at $L$20 and the value may be at $N$22 but the ADDRESS function for the hyperlink returns $C$3 since it's the 3rd row and 3rd column in the lookup range.

Got it (at least one way)...

Match criteria are on worksheet Formulas!D99 and !E99

Lookup range is on worksheet Sources!L11:N62 not including column headers in row 10.

=HYPERLINK(CELL("address", OFFSET(Sources!$L$11,MATCH(Formulas!$D99,Sources!$L$11:$L$62,0)-1, MATCH(Formulas!$E99, Sources!$L$10:$N$10, 0)-1)), IF(OFFSET(Sources!$L$11, MATCH(Formulas!$D99, Sources!$L$11:$L$62, 0)-1, MATCH(Formulas!$E99, Sources!$L$10:$N$10, 0)-1)=0, "No value found", OFFSET(Sources!$L$11, MATCH(Formulas!$D99, Sources!$L$11:$L$62, 0)-1, MATCH(Formulas!$E99, Sources!$L$10:$N$10, 0)-1)))

Excel forces the use of the CELL-OFFSET combination to return the actual address of the cell rather than its relative row and column position in the range. But it has the advantage that the CELL function returns the full path of the file and worksheet so you don't have to put them in square brackets for the HYPERLINK argument if you're in the same spreadsheet.

Now if only Excel would come up with an IFZERO function similar to IFERROR so that you don't have to duplicate your formula in the logical test and the outcome! Even better a generic

IFVALUE(test_condition,if_true,if_false)

So testing the above code for a zero value would give IFVALUE(0,,"Insert rate here"). I tried a UDF but it wouldn't refresh as nicely as a native Excel function.

GMF,

Can you change the formula so that the hyperlink address will return the correct value if the source table does not start at A1?Great question!

I created this formula:

=HYPERLINK("[Locate-a-particular-cell-in-a-table.xlsx]Sheet2!"&ADDRESS(MATCH(Sheet1!A2, Table1[[#All], [Column1]], 0)+CELL("row", Table1)-2, MATCH(Sheet1!B2, Table1[#Headers], 0)+CELL("col", Table1)-1), IF(INDEX(Table1, MATCH(Sheet1!A2, Table1[Column1], 0), MATCH(Sheet1!B2, Table1[#Headers], 0))=0, "Insert rate here", INDEX(Table1, MATCH(Sheet1!A2, Table1[Column1], 0), MATCH(Sheet1!B2, Table1[#Headers], 0))))

Get the Excel *.xlsx file

Locate-a-particular-cell-in-a-tablev2.xlsx

Thanks for sharing!

GMF,

Now if only Excel would come up with an IFZERO function similar to IFERROR so that you don't have to duplicate your formula in the logical test and the outcome! Even better a genericIFVALUE(test_condition,if_true,if_false)

Another great question!

Formula:

=HYPERLINK("[Locate-a-particular-cell-in-a-table.xlsx]Sheet2!"&ADDRESS(MATCH(Sheet1!A2, Table1[[#All], [Column1]], 0)+CELL("row", Table1)-2, MATCH(Sheet1!B2, Table1[#Headers], 0)+CELL("col", Table1)-1), IFERROR(1/(1/(INDEX(Table1, MATCH(Sheet1!A2, Table1[Column1], 0), MATCH(Sheet1!B2, Table1[#Headers], 0)))), "Insert rate here"))

Get the Excel *.xlsx file

Locate-a-particular-cell-in-a-tablev3.xlsx

The IFERROR(1/0) test is good for numeric values but generates the error option if you're returning text (for example, a lookup of tax rates could provide a text range ["1.6% - 7.4%"] for 2013 Idaho state taxes whereas Illinois has a flat 5% formatted as a number). I guess you'd have to tailor the error handling to the type of data you expect to return.

Mere quibbles considering the usefulness of the overall post!