VLOOKUP function in multiply files

Do you want to: Ask a how-to question
OS version: win 11 pro
App version: 8.0.0.99
Downloaded from: ONLYOFFICE website

Hello!

I use the VLOOKUP function to work with tables, but I am faced with the impossibility of specifying a reference in one table to a column and a range in another table. How to do it?

Hello @a.shliakhov

If I understand your scenario correctly, you want to reference the lookup_value from another table to perform VLOOKUP function in separate array of cells. If so, then you can simply reference lookup_value value of the function, for example, like that:
image

On this example, the lookup_value value is referenced from the cell D5 which is not in the table_array of cells A1:B5 and it returns the value 200 respectively to a E5 cell.

If it does not fit your scenario, please provide more information and if possible examples of your tables for better understanding.

in your example, the actions are performed within one file. I need to use this function with data from another file.

VLOOKUP formula uses these values:

Argument Description
lookup_value A value to search for.
table_array Two or more columns containing data sorted in ascending order.
col_index_num A column number in the table_array, a numeric value greater than or equal to 1 but less than the number of columns in the table_array.
range_lookup A logical value TRUE or FALSE. It is an optional argument. Enter FALSE to find an exact match. Enter TRUE or omit this argument to find an approximate match, in this case if there is not a value that strictly matches the lookup_value, then the function will choose the next largest value less than the lookup_value.

Do you want to reference one cell or array of cells of your table in source workbook to another workbook and use it as one of these values in VLOOKUP formula in destination file? Or you’d like to just reference a resulting value of the formula to another workbook (considering you have already declared this function in source workbook)?

I need both options, but the first one is preferable.
MS Excel allows you to specify data from another file here ‘table_array’

I am looking into it, I will provide feedback as soon as any news come up.

If you want to reference data from another workbook, you can use external links to the cell or array of cells as it is described here:
https://helpcenter.onlyoffice.com/ONLYOFFICE-Editors/ONLYOFFICE-Spreadsheet-Editor/UsageInstructions/AddExternalLinks.aspx

In case of VLOOKUP function, as well as other functions actually, you can reference data in such way:

  1. Referencing table_array from Source.xlsx file, for instance:
=VLOOKUP(A1,'[Source.xlsx]Sheet1'!A1:B6,2,TRUE)
  1. Referencing lookup_value from Destination.xlsx file:
=VLOOKUP('[Destination.xlsx]Sheet1'!A1,A1:B6,2,TRUE)

Note: when referencing table_array you may face #REF! error in the cell. In this case you will need to simply double click the cell with formula and then exit editing mode. We are aware of this issue, the work is already in progress.

In this case I’ve used links to different files in short format ('[Destination.xlsx]Sheet1') which is used in case when both files are located in the same folder.

I was able to establish a connection between the files, but I don’t understand what exactly this does, other than the ability to update the information in the “established connections” window :slight_smile:
When selecting bound ranges, no references are added to the function. I have a video comparison of working in MS office and online office - MS allows you to use the function without unnecessary actions. WeTransfer - Send Large Files & Share Photos Online - Up to 2GB Free

Unfortunately, such method is not available in Desktop Editors. We will take closer look at the video and discuss it internally. I’ll let you know the results.

At this moment, I’m afraid that you can only enter values manually.