Online Spreadsheets: VLOOKUP Link To Other Workbook

Suggestions
1

With the offline editor you can link formulas to separate workbooks. For example, I can use a VLOOKUP function to match something in the current spreadsheet to a separate workbook/spreadsheet file and return the result from that separate spreadsheet. This works in the offline editor but fails in the online editor.

Example formula (entered into sheet 1 of “My Test File.xlsx”):
=VLOOKUP(A2,‘[separate lookup file.xlsx]Sheet1’!$A$1:$F$20,2,FALSE)

2

Hello @sporto,
Please let us know which version of the online editor you are using, and if possible, provide the file where the error occurs so we can check it.

4

@Roman I’m using the OO hosted online editor. Version:
Online editors: ONLYOFFICE Docs v.8.3.0.94

Test files are attached. The VLOOKUP to another file works fine if used on a desktop but does not work on the online version (get an #N/A error).
SourceInfo.xlsx (8.6 KB)
VLookupTest.xlsx (10.9 KB)

5

Does it mean that you are using Docs cloud and Document Server is integrated with your own application? Or you mean DocSpace Cloud?

It is important to know in which environment Document Server is used, because this feature must be implemented separately in personal integrations, i.e. when you are integrating Document Server into your own storage application. Please refer to this documentation:

7

@Constantine Sorry for not being clear. I’m not self-hosting, I’m using DocSpace Cloud.

8

Thanks. Unfortunately, it is not that simple with cloud solutions, as the file is not located directly in the same directory as when using Desktop Editors.

Easiest solution here is to create another sheet in the spreadsheet where VLOOKUP will be used and link required table as an external link:
https://helpcenter.onlyoffice.com/ONLYOFFICE-Editors/ONLYOFFICE-Spreadsheet-Editor/UsageInstructions/AddExternalLinks.aspx
Generally, the same approach as the second table in VLookupTest.xlsx file that you’ve provided, but with the reference to external file.

Then you will be able to use VLOOKUP from that sheet.

9

Thanks for looking into this. It will definitely work in most instances. There is one instance I’m dealing with that the workbook must be kept separate so it won’t work there but maybe in the future we’ll have that capability. Have a great day!

1 Like