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?

RAJ.A.D asks:

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.

Answer:

Sheet1

locate a value in a table

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

locate a value in a table1

Array formula in cell C2, sheet1:

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

How to enter an array formula

  1. Select cell C2
  2. Copy (Ctrl + c) and paste (Ctrl + v) above array formula to excel
  3. Press and hold CTRL + SHIFT simultaneously
  4. Press Enter
  5. Release all keys

If you did it right, the formula is now surrounded by curly brackets, like this:

{=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))))}

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