## Locate a particular cell in a table

**Find a value and return a corresponding value is really easy with Excel´s INDEX and MATCH functions. Did you know that you also can quickly "jump to" and select that value in a table using the HYPERLINK function?**

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;

now i want to locate/hyperlink the particular cell intersecting the customer reference and product reference.

### Answer:

**Sheet1**

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

Click on a value in column C (above) and you will instantly select the returned value in the table (below).

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

**Sheet2**

**Array formula in cell C2, sheet1:**

**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 is now surrounded by curly brackets, like this:

### Explaining array formula in cell C2

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

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

becomes

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

and returns 7.

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

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

becomes

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

and returns 2.

**Step 3 - Return intersecting value**

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

becomes

INDEX(Table1, 7, 2)

and returns 41.

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

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)

and returns 41.

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

"[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**

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.

### Download excel *.xlsx file

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

### Functions in this post:

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

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

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

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

Returns the relative position of an item in an array that matches a specified value in a specific order

**ADDRESS(**row_num;column_num**)**

Creates a cell reference as text, given specified row and column numbers

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

Creates a shortcut or jump that opens a document stored on your harddrive, network server or on the internet

### 7 Responses to “Locate a particular cell in a table”

### Leave a Reply

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

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

your 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

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

Download 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"))

Download 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!