Hyperlink to a value from another sheet using match/vlookup

[CatalinI]

Hi

I'm trying to link a value from a sheet to the same value from another sheet. The value from the second sheet might have multiple lines, and I want to see them all when I click the hyperlink. Is it doable?

I'm attaching an example, maybe it would be easier to understand what i'm trying to do.

Thank you

test.xlsx

[sandy666]

maybe it will help:
HYPERLINK
or
HYPERLINK 2

[CatalinI]

Hi Sandy,

Consider me dumb, but I couldn't make it. I've seen that you had the right answer for both topics, maybe you can help me also .

MR Raimund from sheet1 with id 231628, has 3 different categories in sheet2: COMMUNICATIONS TECHNOLOGY, ENTERPRISE CONTENT MANAGEMENT and PARTNER MANAGEMENT. When I click the hyperlink from Hyperlink column, I want to be sent to those 3 rows. If i put the formula like this: =HYPERLINK(VLOOKUP(A2,Sheet2!A:B,2,0)), i get only the first matched value and when I click it, i get an error message: cannot open the specified file.

I reattached the file with this formula inserted.

If you will have time to look a bit over this, I would really appreciate it.

Thanks

[sandy666]

=HYPERLINK("#INDEX('Sheet2'!$B$2:$B$24,MATCH(A2,Sheet2!$A$2:$A$24,0))",INDEX(Sheet2!$B$2:$B$24,MATCH(A2,Sheet2!$A$2:$A$24,0)))
but (always has to be a "but")...
you cannot drag it down or everywhere because link will not change^*), only second part of hyperlink will change (name to click)
so you should update every hyperlink manually.

^*) - first part of hyperlink is between double quotes what is mean this is a text so it cannot change itself

or you can use this:

Please Login or Register  to view this content.

and drag down
Copy of test.v2.xlsx

hyperlink cannot "move" you to multirow

[CatalinI]

Wow! You're a star! Thanks so much.

But we still have that issue with MR Raimund that has multiple categories. Can this be solved with a copy of hyperlink formula on the next cell (column) and somehow skip the first match? And move to the next columns until it gets to the last category.

231628 Mr Raimund COMMUNICATIONS TECHNOLOGY PARTNER MANAGEMENT

[sandy666]

you mean:
for value: 231628
show all matches:
COMMUNICATIONS TECHNOLOGY
ENTERPRISE CONTENT MANAGEMENT
PARTNER MANAGEMENT
APPLICATION DEVELOPMENT TOOLS
CUSTOMER RELATIONSHIP MANAGEMENT
in columns/rows?
if yes you should read more about dependent lists and reorganize your data.
maybe here you will find solution.

EDIT:
UNIQUE_ID from DataValidation List (drop down list) and in next column (or somewhere) results with link
I assume not only Raimund has one ID with multiple category
I know your source data (example) but really don't know what you want to get as results (and how it looks). Any example with results with one ID - one category and one ID and multiple category?

Update .xlsx
Copy of test.v2.2.xlsx
is it ok? but without hyperlinks.