[Solved] VLOOKUP not retrieving info

Discuss the spreadsheet application
Post Reply
User avatar
TalonsPryde
Posts: 16
Joined: Sun Nov 04, 2018 8:41 pm

[Solved] VLOOKUP not retrieving info

Post by TalonsPryde »

I am trying to use vlookup with an outside file. When I reference the file, I put in my term (I used "Test") and have 2 columns after to retrieve data from. All I get is either a blank cell or #N/A even though everything is exactly alike (cell format, text, and source to result). I have search criteria applying to whole cells activated. This is frustrating me to no end. I am effectively trying to do a grocery list for shopping referencing a master grocery list file.

My wife is an accountant and uses vlookup regularly in Excel and helped me figure out how to reference external files.
Last edited by TalonsPryde on Sat Jan 19, 2019 10:10 pm, edited 1 time in total.
Open Office 4.1.6 on windows 7 with sp1
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Vlookup not retrieving info

Post by Zizi64 »

- Are you using absolure or relative URL-s for the another file?
- Is the another file open?

Please upload two ODF type sample files here with some sample data and the formula what you tried to achieve.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
TalonsPryde
Posts: 16
Joined: Sun Nov 04, 2018 8:41 pm

Re: Vlookup not retrieving info

Post by TalonsPryde »

Local file...
file open to get spread.
I will get a sample file set soon...
Open Office 4.1.6 on windows 7 with sp1
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Vlookup not retrieving info

Post by RusselB »

Please note that the format of the VLOOKUP function in Excel, when accessing a secondary source, is different from that of the same function in Calc.
Also please note that, with later versions of Excel, if the secondary source is an open file, Excel uses the data of that open file, rather than the data of the most recently saved version of that secondary source.
Calc (in OpenOffice at least, not sure about Calc for LibreOffice) only accesses the data of the most recently saved version of the secondary source.

So you have to have the secondary source (outside file as you called it) saved before your active file (the one with the VLOOKUP) can read the data, and the data has to be valid.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
TalonsPryde
Posts: 16
Joined: Sun Nov 04, 2018 8:41 pm

Re: Vlookup not retrieving info

Post by TalonsPryde »

Oh wow. That really helps. So I have to save the file first, close it, then refresh the calling file to get it to work. I had no idea. Thank you so much.
Open Office 4.1.6 on windows 7 with sp1
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Vlookup not retrieving info [Solved]

Post by RusselB »

Well, you don't have to close it, but the calling file will only work with the data that is in the saved file.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Post Reply