Lookup to find a match between two sheets and hyperlink the corresponding column value

[crkbrtk]

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.

[#2]

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.

[#3]

Hi @crkbrtk 

The following works as well here (365 Current Channel):

IF(<matched>,
  HYPERLINK(<link_in_thisworkbook>, <Key>),
  HYPERLINK("", "No Match")
)

[#4]

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

[#5]

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

[#6]

@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.

[#7]

@crkbrtk 

To clarify things & help you as best as possible, could you:

• Post a cropped picture or your 2 sheets
• Indicate which version of Excel you run please

[#8]

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

[#9]

Version 2406 (Build 17726.20206 Click-to-Run)

[#10]

@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

[#11]

@m_tarler 

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

[#12]

@Lorenzo  That is because the ' should only be around the sheet name and not include the file name.  I tweaked your version to include a generic sheet reference lookup

[#13]

@m_tarler WOW, you're Amazing!!!

[#14]

@m_tarler Not sure what am I doing wrong but the sheet1 key column still reflects incorrect data.

Please find my attached test sheet. 

[#15 · Accepted solution]

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

[#16]

@crkbrtk 

@m_tarler doesn't seem avail. right now.... Looked at this yesterday and thanks to the file you share things are clear now 🙂

Re. Not sure what am I doing wrong but the sheet1 key column still reflects incorrect data

A Reference is wrong in the file you share + the formula refers to SheetRef that doesn't exist

Attached file has your data + dummy ones, formatted as Tables (TableJIRA & TableKEY). Formula is a bit longer:

=IF(ISBLANK([@[JIRA ID]]), "",
  LET(
    IdMatch,      XMATCH("*" & [@[JIRA ID]] & "*", TableKEY[Links], 2),
    IF(ISNA(IdMatch),
      HYPERLINK("", "No Match"),
      LET(
        keysPath, TEXTBEFORE(CELL("address", TableKEY[#Headers]), "$"),
        KEY,      INDEX(TableKEY[Key], IdMatch),
        keyAddr,  ADDRESS(
                    IdMatch + ROW(TableKEY[#Headers]),
                    COLUMN(TableKEY[Links])
        ),
        HYPERLINK(keysPath & keyAddr, IF(LEN(KEY) = 0, "*** ??? ***", KEY) )
      )
    )
  )
)

NB: it doesn't account for the Workbook/Sheetname with spaces or other char. (cf. discussion between @m_tarler & I above)

[#17]

Thank you for the detailed information and providing the formula. It totally worked. Really appreciate it.