XLOOKUP TO RETUN HYPERLINK.

[TEXASFOREVER1936]

I have a spread sheet named "One Size Look Up (OSLU)" that can return numerous values of a certain criteria with XLOOKUP functions. I am trying to format a cell, in OSLU, to return a hyperlink to a local file on my PC that would route me to the criteria's profile. The hyperlink is located on a separate spread sheet named "CHARTS" within the same workbook. I have wrapped my XLOOKUP in a hyperlink formula and it returns the error message "CANNOT OPEN THE SPECIFIED FILE". The hyperlink opens correctly in its originated spread sheet, CHARTS. The return value of the XLOOKUP, wrapped in the hyperlink formula, does not. Any ideas? 

Thank you so much! 

[#2]

@TEXASFOREVER1936 

Try adding the path to the hyperlink address.

[#3]

@TEXASFOREVER1936 

In my case all this works

[#4]

@Sergei Baklan When I try "=HYPERLINK((XLOOKUP(C16,CHARTS!$GO$4:$GO$432,XLOOKUP(OSLU!D16,CHARTS!$GQ$2:$HA$2,CHARTS!$GQ$4:$HA$432))))" I get the following results when I click on the returned Hyperlink (see attached). Also, my hyperlink address is not routed to Onedrive nor is the file I'm trying to Hyperlink. It is stored on a local drive.

[#5]

@TEXASFOREVER1936 

If all the files are in the same location, you might want to try adding a Hyperlink Base to the workbook's properties.

File | Info | Properties | Advanced Properties.
My Hyperlink base for the test I ran: C:\Users\MyUserName\MyOneDriveName\Desktop\Test
Then in the sheet I can use HYPERLINK on the name of the file to open. Path not needed.

[#6]

@TEXASFOREVER1936 

It is forwards on OneDrive if you current file is located on OneDrive, doesn't matter where is the target file. Its file path is not recognized correctly, thus Excel takes current folder and tries to find file here.

Most probably your issue is not in XLOOKUP formula, but in hyperlink it returns. Something shall be corrected here or by formula. Without knowing which exactly link text formula returns it's hard to say more concrete.

As an example, if my current file is on OneDrive and target file is local drive, link returned as '[C:\Test\testA.xlsx]Sheet2'!$A$1 forwards on OneDrive, [C:\Test\testA.xlsx]'Sheet2'!$A$1 opens local file and focuses on right place.