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)
=IF([conditional], HYPERLINK("[this_wkbookname]Sheet2!B2","EL-2635"), HYPERLINK("[this_wkbookname]Sheet1!B2","No Match"))
note I added the second hyperlink for the No Match to link to the same cell it is in so you don't get an error if they click on "No Match"
note this format [bookname]sheet!cell is used for 'local' links but that won't work for excel online.
You can also use LET() to assign the lookup result to a temp variable if that is needed/easier.
Sheet2
JIRA ID Key from Sheet 1
DS-73
Sheet1
Key Links
EL-26353 O-104, O-108, O-107, O-109, O-112, DS-73
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.
- This totally helped me getting to my resolution. Thank you.
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
