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:
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:
=VLOOKUP(A1,'[Source.xlsx]Sheet1'!A1:B6,2,TRUE)
=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 
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.
We have registered an enhancement suggestion to add possibility to reference external links to cells to destination workbook from source workbook by selecting them. Unfortunately, I cannot provide ETA at the moment. Once such feature is introduced, we will notify you.
Thank you!
Waiting for it