Forum Discussion
Lookup to find a match between two sheets and hyperlink the corresponding column value
crkbrtk try the attached. I updated the formula in a couple places:
=LET( JiraIDs, A5, Key, Sheet2!$A$2:$A$100, Links, Sheet2!$B$2:$B$100, SheetRef, SUBSTITUTE(SUBSTITUTE(TEXTBEFORE(CELL("address",INDEX(Links,1)),"!"),"'",""),"]","]'")&"'!", found, XMATCH("*"& JiraIDs &"*", Links,2), IF(ISNUMBER(found), HYPERLINK(SheetRef&ADDRESS(ROW(INDEX(Links,found)),COLUMN(INDEX(Links,1))),INDEX(Key,found)), HYPERLINK("","No Match") ) )so line 3 above needs to point to the actual range for the key and links (before you had 'Sheet 2' which had a space in the tab name so excel didn't recognize that. I would recommend updating sheet 2 data to be in a named TABLE (format as table on the home tab and then click in the table tab and give it a name) so then you can use 'Structured Reference' like LinkTable[Links] but that might be something to learn later.
I also added line 4 because line 7 was using the name 'SheetRef' found in the other workbook as that formula we created to generate how to properly reference Sheet2 (or whatever that sheet's name is in your workbook). So I added line 4 and added that calculation. I would actually recommend you create a workbook name so excel only has to do that 1 time instead of every cell but again that might be something you will need to learn more about.
Finally I updated line 7 to use COLUMN(INDEX(Links,1)) because before I hardcoded 2 for column B but if you move this to another book or change the reference that Links in line 3 points to this new version will automatically update.
PS please NOTE that this formula uses CELL() function and that function is NOT supported in the online version of excel. but neither is the HYPERLINK() function for 'local' links (i.e. to another cell inside the workbook)
crkbrtk Not sure I/we understand what you need. I have attached a sample sheet for what I think you wanted but you will have to let us know what isn't right
EDIT: I updated the file so now the JIRA ID column is automatically generated from the list in A2 and then the items in the Key column are autogenerated based on the list under JIRA ID
EDIT2: I found that if I return an ARRAY (as done in column D) the hyperlink doesn't work but if I only return a single value (and copy/fill down) then the hyperlink does work.
How do I change the below formula to specify all the column values from Sheet2 instead of a single column value.
=IF(ISNUMBER(SEARCH(A6,Sheet2!$B$2)),
HYPERLINK("[Link_Sample.xlsx]Sheet2!B2",Sheet2!$A$2),
HYPERLINK("","No Match"))
Is the below accurate?
=IF(ISNUMBER(SEARCH(A6,Sheet2!$B$B)),
HYPERLINK("[Link_Sample.xlsx]Sheet2!B:B",Sheet2!$A$A),
HYPERLINK("","No Match"))
crkbrtk still struggling to understand what your sheet actually looks like. I'm assuming you meant all/many rows and not columns. Here is example for ~100 rows. I don't recommend using full columns if possible
=LET(JiraIDs, A5, Key, Sheet2!$A$2:$A$100, Links, Sheet2!$B$2:$B$100, found, XMATCH("*"& JiraIDs &"*", Links,2), IF(ISNUMBER(found), HYPERLINK("[Link_Sample.xlsx]Sheet2!B" & ROW(INDEX(Links,found)),INDEX(Key,found)), HYPERLINK("","No Match")))see attached
Took a similar approach (XMATCH) and looked at not hard-coding string
<[ThisWorkbook]TargetSheetName>
After some of investigations it seems there's a limitation (expected ???) where HYPERLINK doesn't seem to support the single-quotes added to the above string when either ThisWorkbook and/or TargetSheetName have a non-AlphaNum char. (but the underscore) in their names
Ex. If ThisWorkbook is saved as This-Workook.xlsx, the reference to A5 in Sheet2 would be '[This-Workook.xlsx]Sheet2'!A5. The corresponding HYPERLINK doesn't work (Cannot open the specified file)In attached file I renamed Sheet2 as Sheet$2 and revised your formula accordingly ==> Cannot open the specified file
m_tarler Not sure what am I doing wrong but the sheet1 key column still reflects incorrect data.
Please find my attached test sheet.crkbrtk try the attached. I updated the formula in a couple places:
=LET( JiraIDs, A5, Key, Sheet2!$A$2:$A$100, Links, Sheet2!$B$2:$B$100, SheetRef, SUBSTITUTE(SUBSTITUTE(TEXTBEFORE(CELL("address",INDEX(Links,1)),"!"),"'",""),"]","]'")&"'!", found, XMATCH("*"& JiraIDs &"*", Links,2), IF(ISNUMBER(found), HYPERLINK(SheetRef&ADDRESS(ROW(INDEX(Links,found)),COLUMN(INDEX(Links,1))),INDEX(Key,found)), HYPERLINK("","No Match") ) )so line 3 above needs to point to the actual range for the key and links (before you had 'Sheet 2' which had a space in the tab name so excel didn't recognize that. I would recommend updating sheet 2 data to be in a named TABLE (format as table on the home tab and then click in the table tab and give it a name) so then you can use 'Structured Reference' like LinkTable[Links] but that might be something to learn later.
I also added line 4 because line 7 was using the name 'SheetRef' found in the other workbook as that formula we created to generate how to properly reference Sheet2 (or whatever that sheet's name is in your workbook). So I added line 4 and added that calculation. I would actually recommend you create a workbook name so excel only has to do that 1 time instead of every cell but again that might be something you will need to learn more about.
Finally I updated line 7 to use COLUMN(INDEX(Links,1)) because before I hardcoded 2 for column B but if you move this to another book or change the reference that Links in line 3 points to this new version will automatically update.
PS please NOTE that this formula uses CELL() function and that function is NOT supported in the online version of excel. but neither is the HYPERLINK() function for 'local' links (i.e. to another cell inside the workbook)
