VLOOKUP and return multiple relative hyperlinks

[lulla000]

I have a complex data set for which I would like to create a search function. A search of the code name should return working links to all documents in the row referencing that code. The data set of 1150 codes is almost complete so we are not looking to reconfigure it.

Relative hyperlinks to the documents have been created but when using the VLOOKUP code, a message pops up saying 'Cannot open the specified file'.

I have attached a mock workbook on Sheet1 and have been working on creating a VLOOKUP search on Sheet2.

Sheet3 is what we initally hoped a search page would look like but it feels like that is probably a long shot hah

More details below

Overview
- I have created a workbook directory with a list of codes
- Each code has a unique description and belongs to a category
- On the same row as the code, relative links and friendly names to documents related to each code are contained in cells D-I.
- All relative links are working.

Objective
- To create a Vlookup that returns all documents related to a code.
- The data must be secured - we require only certain people to have editting permission
- It must be scalable

[Glenn Kennedy]

Can you show your expected results from YOUR conditions, and not something that bears no relationship to your data.... In what way is your current formula NOT working? What do you want it to show/do?

[sandy666]

try this:

Please Login or Register  to view this content.

[lulla000]

Apologies if my explanation was not clear enough. I've not done anything like this before and wanted to see if a search of this kind could actually be done.

I would like to create a search function for the database where a search by code name would return all relative linked documents containing that code.

It is possible my current formula is not what is required to perform the search. If you have any other ideas/suggestions, I am all ears.

On sheet2, I have tried to create a Vlookup to return the relative link to the DDD Drawing document. When entering the formula

=HYPERLINK(VLOOKUP(lookup_value, table_array, col_index, [range_lookup]), VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup],0))

The relative link appears as a working link but when I click on it, a message pops up saying 'Cannot open the specified file.'

Does this answer your questions?

[lulla000]

Thank you @sandy666. I have tried the formula you suggested and the same message pops up 'Cannot open the specified file.'

[sandy666]

VLOOKUP is not designed (complicated ) to be a hyperlink because VLOOKUP return value not address of the cell, that is why I used ADDRESS (much easier) and it works. Did you check this on my attachment (your file)? It should work and after click move to another sheet and select target value.
About database: did you try any D-functions? They are just designed to database.
If you want I'll attach example (without description , sorry) how it works.

Edit: I added example (3 files: xlsx, docx and pdf) and it works. First click jumping from sheet2 to packaging to proper place, second click open file.
Try APC checklist | AAA and BBB

With your paths you can try this "[File 1\AAA\Approved documents\AAA_Drawing.docx]"
Path should be a full, ie. =HYPERLINK("B:\Folder 1\Folder 2\folder3\Folder4\ads.xlsm","ads") or if files are in the same folder: =HYPERLINK("ads.xlsm","ads")
also you can try set links to your documents from INSERT | LINKS | LINK (shortcut: Left Alt, N, I2)