Excel Hyperlink to the source of CELL-INDEX-MATCH searching in an array of cells in several columns

Question

HYPERLINK BACK TO INDEX MATCH SOURCE

Hi all,

I am trying to link two tables in Excel 2010. I have uploaded a simplified version of the tables in the screenshot in the link at the top [HYPERLINK BACK TO INDEX MATCH SOURCE].

Note: The tables are in separate sheets in the original file and are next to each other in this case for simplification purposes. Thus any solution will need to work cross-sheet.

The table on the right has user input of the letters A to E in random slots on each row. Based on that input, I then have a countif conditional formatting (yellow highlight) to highlight the matching entries between the user input and the schedule in the table on the left. As you can see, the matching entries are highlighted in yellow. All is good until the next step, when now my user would like to be able to click on the highlighted cells in the left table and go to the original entry in the right table. After long hours of research, the best option to do that I could find was this:

=IFERROR(HYPERLINK("#"&CELL("address",INDEX(Slot1,MATCH(B3,Slot1,0))),B3),"")

You can most probably recognize an INDEX-MATCH combination, which puts the original entry on the right into the table on the left. I then have a hyperlink leading to the original entry. I have created Named Ranges for each Slot column, i.e. Slot1 for Column I, Slot2 for Column J, etc. The big problem here is that INDEX-MATCH can only search in one column, and I need it to search in several ones because the value I am searching for is not limited to one column. For example, "B1" might be anywhere in the range I3:M7, but with INDEX-MATCH I can only search for it in column J, for example. In the sample table "B1" is in column I, so that is why there is no link appearing in cell B6.

So here is my question: Is there any way to have a hyperlink from a cell in the LEFT TABLE searching for corresponding entry in the RIGHT TABLE in the range I3:M7? Preferably no VBA and only Excel functions please. Also, please keep in mind that the two tables are next to each other just for simplification, and so any solution would need to work cross-sheet.

Answer 1

Ok I think I got something close :

{=IFERROR(HYPERLINK("#"&ADDRESS(MATCH(F$1,$H$2:$H$6,0)+1,MATCH(1,(F8=OFFSET($I$1:$M$1,MATCH(F$1,$H$2:$H$6,0),0))*1,0)+8),F8),"")}

This is the formula when positioned in cell F9 in screenshot below :

Make sure to validate with SHIFT+MAJ+ENTER

You might need to adapt the +1 and +8 depending on where your data is starting.

Edit for data on separate sheet :

The first +1 in the formula is related to the starting row of your data table and is equal to starting row - 1. The second +1 in the formula is related to the starting column of your data table and is equal to starting column index - 1.

Formula from B3 in Sheet1 :

{=IFERROR(HYPERLINK("#"&"Sheet2!"&ADDRESS(MATCH(B$1,Sheet2!$A$2:$A$6,0)+1,MATCH(1,(B2=OFFSET(Sheet2!$B$1:$F$1,MATCH(B$1,Sheet2!$A$2:$A$6,0),0))*1,0)+1),B2),"")}

Sheet2 as follow:

Answer 2

Try this in B4 then fill right and copy that row down to subsequent rows.

=IF(COUNTIF($I$3:$M$7, B3), HYPERLINK("#"&ADDRESS(MATCH(--RIGHT(B3), $H$3:$H$7, 0)+2, MATCH(B3, INDEX($I$3:$M$7, MATCH(--RIGHT(B3), $H$3:$H$7, 0), 0), 0)+8, 4, 1), B3), TEXT(,))