Forum Discussion
Lookup to find a match between two sheets and hyperlink the corresponding column value
Hello Experts.
Seeking to solve the below:
I've go two sheets as mentioned below - and need to lookup to find a match in both sheets and then populate the value of the corresponding column by hyperlinking to it.
Sheet 1:
| JIRA ID |
| DS-228, DS-759 |
Sheet 2:
| Key | Links |
| EL-2635 | O-001, DS-759, DS-781 |
Desired Output:
Sheet 1
JIRA ID | Key |
DS-759 | EL-2635 |
DS-228 | No Match |
EL-2635 in desired output must be hyperlinked to the value in Sheet2.
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)
- Appreciate your response. Is this supposed to be executed in Excel or somewhere else? If Excel - it doesn't seem to be working.
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
- You can use the HYPERLINK() function but note that may/will not work in the online version of Excel. Here is an example:
=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.- Appreciate your response. However, when I tried your formula it doesn't bring the key column value from Sheet2 into Sheet1 key from sheet1 column and hyperlink the value in sheet1 to sheet2.
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-73crkbrtk 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.
