[Solved] Need Help With LO Calc and the VLOOKUP Function

Questions about applications and software
Forum rules
Before you post please read how to get help
Post Reply
User avatar
Portreve
Level 8
Level 8
Posts: 2184
Joined: Mon Apr 18, 2011 12:03 am
Location: Florida
Contact:

[Solved] Need Help With LO Calc and the VLOOKUP Function

Post by Portreve » Wed Oct 02, 2019 2:23 pm

Background:
The better part of three years ago, I created a tool — in actuality, a spreadsheet — for use with my job. I've since gone on to create several tools, which over time I evolved and developed, and which have now been reduced to their final state, with just two used for my entire personal workflow relative to data I use with my job.

A key part of their functionality is the VLOOKUP function. For those reading this thread just to see what it's about, VLOOKUP is a standard spreadsheet function which lets you turn a spreadsheet into a reasonably functional database by letting you set up a given field and then supply you with content based on what you enter into that field. As you may appreciate, the more sophisticated your design, the more sophisticated the results can be.

The tool I need help with — for which I have also posted queries on LibreOffice's online help community — is the one I use to plan my weeks out.

For my job, I travel to a store a day, five days a week, with a crew, and we audit that store's inventory. The company I work for is not really good about helping its employees and it certainly isn't good at all with providing useful tools for us, so I've taken all the stores in my state that are part of my business unit, gone over the address list and meticulously verified the addresses to every single store (many of the addresses in our official company store list have errors of various kinds, a number of which are just flat-out wrong, and it's just not considered a priority to respond to requests to fix addresses) and then, having verified the addresses, I have used Google's Maps tool to calculate the distance and drive time from my home to every single one of them. This list is the better part of 600 stores long.

I have six sheets within the file. The first is a data entry interface, allowing me to enter up to four weeks' worth of stores, along with the date for the first day of each week, and provisions for typing in the names of the people I am working with.

The second, third, fourth, and fifth sheets are basically weekly report forms which then give me what district the store is a part of, what the address is, what the phone number is, who the district manager is, what the physical size of the store is, how much time I should budget to drive there and how many miles that will be, and then how much I can bill the company for for both drive time and mileage, either for a round trip (which is the norm) or a one-way distance for special purposes. (At this point, I print the thing out and use that for a physical record but also as the basis to build a set of Google Calendar events.)

The sixth sheet is a massive data table holding all of the store data, and is the place from which VLOOKUP summons everything for the four weekly report sheets.

The Problem (And What I Need Help With):
Recently, I've seen fit to create a new version of this tool because there's now certain other information I need to be able to retrieve automatically. I redesigned the layout of the report pages to better suit the change and account for things I've learned along the way. Because of layout changes, the exact cells I am using VLOOKUP with has changed, and I've fully accounted for that. I've copied-n-pasted a working VLOOKUP "formula" and then simply tweaked which cell for it to look at, and which column's data I want displayed. And, it works: perfectly.

But...

I can summon data in this way for anything I want from columns 2 - 13. It works like a champ. The moment I specify column 14 and beyond, I get a 502 error.

I have even experimentally taken the original formula from the original file and created another spot within that original file and entered the equivalent change, and I get a 502 error there as well. I did this because I wanted to rule out there being anything specific to the new file.

Here's the formula I'm using. It also includes an IF statement because I want the cell to be empty when no data has been entered on the initial master data entry page for that particular day, in any of that particular day's no-data-containing cells.

Code: Select all

=IF(H6=0,"",(VLOOKUP(H6,$'Data Lookup'.A:$'Data Lookup'.M,6,0)))    <--- This formula returns valid data.
=IF(H6=0,"",(VLOOKUP(H6,$'Data Lookup'.A:$'Data Lookup'.M,14,0)))   <--- This formula returns "Err:502".
Anyhow, I am at the limit of my knowledge and really can't troubleshoot this further on my own. I'm hoping someone sees something wrong in the formula (like a fundamental mistake where it will work but it will have issues in some situations, etc.)

If necessary, I will be happy to send a copy of this file to someone with dummy equivalent data instead of sensitive internal data, and they can mess around with the file and perhaps discover something I've overlooked, or just wouldn't even know is a problem.


Thanks so much in advance, folks!
Last edited by Portreve on Fri Oct 04, 2019 3:41 am, edited 2 times in total.
Zen Buddhism: The Journey Is The Reward
Realist: The Gurney Is The Reward

Remember to mark your fixed problem [SOLVED].

“They said my computer wasn't strong enough for Windows 10, so I had to get one with more strongerness.”
— Overheard random customer

User avatar
AndyMH
Level 9
Level 9
Posts: 2666
Joined: Fri Mar 04, 2016 5:23 pm
Location: Wiltshire

Re: Need Help With LO Calc and the VLOOKUP Function

Post by AndyMH » Wed Oct 02, 2019 4:42 pm

Don't use lo, but have used Excel for many years. Assuming vlookup is the same, you are looking up a range from column A to M = 13 column s. Your lookup is trying to return column 14 hence the error. Expand the width of the range you are looking up.

Also, I always set the range lookup flag to false at the end to ensure an exact match, otherwise Excel returns the closest match. And to work correctly the column being looked up must be sorted. With range lookup = false it doesn't have to be sorted.
Homebrew i5-8400+GTX1080 Cinnamon 19.0, 3 x Thinkpad T430 Cinnamon 19.0, i7-3632 , i5-3320, i5-3210, Thinkpad T60 19.0 Mate

User avatar
lsemmens
Level 9
Level 9
Posts: 2670
Joined: Wed Sep 10, 2014 9:07 pm
Location: Rural South Australia

Re: Need Help With LO Calc and the VLOOKUP Function

Post by lsemmens » Wed Oct 02, 2019 7:26 pm

Silly question. Why persist with a spreadsheet for database functionality when you have a perfectly good database as part of the Office Suite?
Kernel: 4.15.0-46-generic x86_64 bits
Desktop: Cinnamon 3.8.9
Distro: Linux Mint 19 Tara

Laptop HP-ProBook-470-G2 8Gb RAM SSD
Server AMD Phenom 9650 - GEForce 9400GT 6Gb RAM
+ three other Mint machines
Out of my mind - please leave a message

User avatar
Portreve
Level 8
Level 8
Posts: 2184
Joined: Mon Apr 18, 2011 12:03 am
Location: Florida
Contact:

Re: Need Help With LO Calc and the VLOOKUP Function

Post by Portreve » Thu Oct 03, 2019 4:05 am

lsemmens wrote:
Wed Oct 02, 2019 7:26 pm
Silly question. Why persist with a spreadsheet for database functionality when you have a perfectly good database as part of the Office Suite?
Everything I'm dealing with, vis a vis my job, revolves around spreadsheets. To be honest, I've never given any thought to using LO's database program. Besides that, I'm using both current tools with Google's cloud-based office suite.
Zen Buddhism: The Journey Is The Reward
Realist: The Gurney Is The Reward

Remember to mark your fixed problem [SOLVED].

“They said my computer wasn't strong enough for Windows 10, so I had to get one with more strongerness.”
— Overheard random customer

User avatar
lsemmens
Level 9
Level 9
Posts: 2670
Joined: Wed Sep 10, 2014 9:07 pm
Location: Rural South Australia

Re: Need Help With LO Calc and the VLOOKUP Function

Post by lsemmens » Thu Oct 03, 2019 5:21 am

It's a pity you weren't a few thousand miles closer, I'd volunteer to visit you and have a look at if a database is appropriate to your needs. Whilst base is not all that hard to learn, some of the concepts of database development may require a few new concepts, which, once understood, do make it a lot easier.
Kernel: 4.15.0-46-generic x86_64 bits
Desktop: Cinnamon 3.8.9
Distro: Linux Mint 19 Tara

Laptop HP-ProBook-470-G2 8Gb RAM SSD
Server AMD Phenom 9650 - GEForce 9400GT 6Gb RAM
+ three other Mint machines
Out of my mind - please leave a message

deepakdeshp
Level 16
Level 16
Posts: 6238
Joined: Sun Aug 09, 2015 10:00 am

Re: Need Help With LO Calc and the VLOOKUP Function

Post by deepakdeshp » Thu Oct 03, 2019 6:33 am

Portreve wrote:
Thu Oct 03, 2019 4:05 am
lsemmens wrote:
Wed Oct 02, 2019 7:26 pm
Silly question. Why persist with a spreadsheet for database functionality when you have a perfectly good database as part of the Office Suite?
Everything I'm dealing with, vis a vis my job, revolves around spreadsheets. To be honest, I've never given any thought to using LO's database program. Besides that, I'm using both current tools with Google's cloud-based office suite.
May be worth a try. After the initial learning curve things would be easy.
If I have helped you solve a problem, please add [SOLVED] to your first post title, it helps other users looking for help, and keeps the forum clean.
Regards,
Deepak

I am using Mint 19.2 Cinnamon 64 bit with AMD A8/7410 processor . Memory 8GB

User avatar
AndyMH
Level 9
Level 9
Posts: 2666
Joined: Fri Mar 04, 2016 5:23 pm
Location: Wiltshire

Re: Need Help With LO Calc and the VLOOKUP Function

Post by AndyMH » Thu Oct 03, 2019 6:45 am

Nothing wrong with using a spreadsheet as a database - depending on what you are doing. I used to estimate the costs of multi-billion pound projects - literally 1,000's of estimates and some very large spreadsheets (and a lot of VBA). I used the lookup functions extensively to extract information and manipulate it.

To illustrate my earlier comment (I was on my tablet) about the use of the range-lookup flag:

No range-lookup flag, data not sorted = wrong answer
Workspace 1_031.png

Range-lookup set FALSE, data not sorted, correct answer
Workspace 1_032.png
I assume LO calc will be working the same way as excel.
Homebrew i5-8400+GTX1080 Cinnamon 19.0, 3 x Thinkpad T430 Cinnamon 19.0, i7-3632 , i5-3320, i5-3210, Thinkpad T60 19.0 Mate

rene
Level 12
Level 12
Posts: 4415
Joined: Sun Mar 27, 2016 6:58 pm

Re: Need Help With LO Calc and the VLOOKUP Function

Post by rene » Thu Oct 03, 2019 6:49 am

AndyMH already provided the answer but would it not be much clearer to say

Code: Select all

LOOKUP(H6; $'Data Lookup'.A:A; $'Data Lookup'.F:F; 0)
so as to just explicitly name the column from which to get the value (F) rather than use "6"? I in fact have trouble thinking of when VLOOKUP() would ever be useful/better...

User avatar
AndyMH
Level 9
Level 9
Posts: 2666
Joined: Fri Mar 04, 2016 5:23 pm
Location: Wiltshire

Re: Need Help With LO Calc and the VLOOKUP Function

Post by AndyMH » Thu Oct 03, 2019 6:56 am

The problem with lookup (in excel assuming lo calc the same) is that it suffers the same problem as vlookup - it will only return the closest match and the data must be sorted. Unfortunately there is no way of fixing this with lookup, while vlookup has the range flag that can be used to solve the problem with unsorted tables. Been there, done it and had problems :) . You can do the equivalent of a lookup and fix the problem with a mix of the index and match functions, but that is starting to complicate things.
Homebrew i5-8400+GTX1080 Cinnamon 19.0, 3 x Thinkpad T430 Cinnamon 19.0, i7-3632 , i5-3320, i5-3210, Thinkpad T60 19.0 Mate

rene
Level 12
Level 12
Posts: 4415
Joined: Sun Mar 27, 2016 6:58 pm

Re: Need Help With LO Calc and the VLOOKUP Function

Post by rene » Thu Oct 03, 2019 7:03 am

Yes, you are right; that fourth parameter "0" that I used above was a bit of wishful thinking it seems: https://help.libreoffice.org/Calc/Sprea ... ons#LOOKUP
Additionally, the search vector for the LOOKUP must be sorted ascending, otherwise the search will not return any usable results.

If LOOKUP cannot find the search criterion, it matches the largest value in the search vector that is less than or equal to the search criterion.
Never mind me...

User avatar
Portreve
Level 8
Level 8
Posts: 2184
Joined: Mon Apr 18, 2011 12:03 am
Location: Florida
Contact:

Re: Need Help With LO Calc and the VLOOKUP Function

Post by Portreve » Thu Oct 03, 2019 7:39 pm

AndyMH wrote:
Wed Oct 02, 2019 4:42 pm
Don't use lo, but have used Excel for many years. Assuming vlookup is the same, you are looking up a range from column A to M = 13 column s. Your lookup is trying to return column 14 hence the error. Expand the width of the range you are looking up.

Also, I always set the range lookup flag to false at the end to ensure an exact match, otherwise Excel returns the closest match. And to work correctly the column being looked up must be sorted. With range lookup = false it doesn't have to be sorted.
Thank you, thank you, THANK YOU!!!

I had absolutely no idea to what the "M" referred. I have adjusted the formula so that it now uses "Z", which gives me plenty of room for future growth and expansion, should I need it. I'm pretty confident I won't but, hey, you never know.
lsemmens wrote:
Thu Oct 03, 2019 5:21 am
It's a pity you weren't a few thousand miles closer, I'd volunteer to visit you and have a look at if a database is appropriate to your needs. Whilst base is not all that hard to learn, some of the concepts of database development may require a few new concepts, which, once understood, do make it a lot easier.
I certainly appreciate the thought. :)


Here's the finished project:

Image
Zen Buddhism: The Journey Is The Reward
Realist: The Gurney Is The Reward

Remember to mark your fixed problem [SOLVED].

“They said my computer wasn't strong enough for Windows 10, so I had to get one with more strongerness.”
— Overheard random customer

User avatar
lsemmens
Level 9
Level 9
Posts: 2670
Joined: Wed Sep 10, 2014 9:07 pm
Location: Rural South Australia

Re: [Solved] Need Help With LO Calc and the VLOOKUP Function

Post by lsemmens » Fri Oct 04, 2019 8:23 am

I'm guessing that you are a bit of a Trekkie, along with smatterings for Dr Who and a few other popular shows. :D
BOT
Looks like a great project for a database. I'm guessing that the details of the store address are re-used regularly, along with audit team and so forth. It must be nightmare to update records if something changes, like billing address, store manager, phone numbers and the like.

With a properly normalised database if one item is updated, then all items relating to that item are automatically updated.
Kernel: 4.15.0-46-generic x86_64 bits
Desktop: Cinnamon 3.8.9
Distro: Linux Mint 19 Tara

Laptop HP-ProBook-470-G2 8Gb RAM SSD
Server AMD Phenom 9650 - GEForce 9400GT 6Gb RAM
+ three other Mint machines
Out of my mind - please leave a message

User avatar
AndyMH
Level 9
Level 9
Posts: 2666
Joined: Fri Mar 04, 2016 5:23 pm
Location: Wiltshire

Re: [Solved] Need Help With LO Calc and the VLOOKUP Function

Post by AndyMH » Fri Oct 04, 2019 12:40 pm

Thank you, thank you, THANK YOU!!!
Happy to help, I first used Excel in 1988 on a mac and have used it ever since (prior to that I was a SuperCalc fan). Learnt a lot over the years, made many mistakes and still learning.
Homebrew i5-8400+GTX1080 Cinnamon 19.0, 3 x Thinkpad T430 Cinnamon 19.0, i7-3632 , i5-3320, i5-3210, Thinkpad T60 19.0 Mate

User avatar
Portreve
Level 8
Level 8
Posts: 2184
Joined: Mon Apr 18, 2011 12:03 am
Location: Florida
Contact:

Re: [Solved] Need Help With LO Calc and the VLOOKUP Function

Post by Portreve » Fri Oct 04, 2019 4:39 pm

lsemmens wrote:
Fri Oct 04, 2019 8:23 am
I'm guessing that you are a bit of a Trekkie, along with smatterings for Dr Who and a few other popular shows. :D
You missed the Babylon 5 references! :wink: :lol:
Looks like a great project for a database. I'm guessing that the details of the store address are re-used regularly, along with audit team and so forth. It must be nightmare to update records if something changes, like billing address, store manager, phone numbers and the like.
This is my own private thing (that is, I had to build it on my own and maintain it on my own) and there's a lot of work that's fine into it simply because the prices we do every week is completely repetitive with, as you have already noted, billable mileage and so on. I see no point in doing the calculations manually, which is what the company has left us with simply because (mileage and drive time data aside) they cannot be bothered to build or even reliably maintain their own internally-used data. I can't begin to tell you how many addresses are wrong and no matter how many times they are reported and by how many people, they are never, ever fixed, even though the correct addresses are maintained and available through other company assets (our public web site's store finder, for instance) and it's just plain pathetic.

Nevertheless, I agree with you: this probably would be a great project for a database.
With a properly normalised database if one item is updated, then all items relating to that item are automatically updated.
Stores are regularly reassigned to different districts (no idea why) and of course there's turnover in middle management, so we get different districts managers and so yes, it would be great to only have to do the "heavy lifting" for each store once, and make the process of changing the stuff that changes quick and easy.

Presently, it is not. It's not a horrific process, but it isn't a snap, either.
Zen Buddhism: The Journey Is The Reward
Realist: The Gurney Is The Reward

Remember to mark your fixed problem [SOLVED].

“They said my computer wasn't strong enough for Windows 10, so I had to get one with more strongerness.”
— Overheard random customer

User avatar
lsemmens
Level 9
Level 9
Posts: 2670
Joined: Wed Sep 10, 2014 9:07 pm
Location: Rural South Australia

Re: [Solved] Need Help With LO Calc and the VLOOKUP Function

Post by lsemmens » Sat Oct 05, 2019 3:00 am

I'm sorry I'm not closer, I'd love to sink my teeth into this one. It could be done by remote control, but, I'd not be comfortable not being "around the corner", so to speak, if something goes wobbly when I'm not home. I would suggest that you start playing with Base but, only with small stuff, first. Say, set up a name and address database and build on it from there.
Kernel: 4.15.0-46-generic x86_64 bits
Desktop: Cinnamon 3.8.9
Distro: Linux Mint 19 Tara

Laptop HP-ProBook-470-G2 8Gb RAM SSD
Server AMD Phenom 9650 - GEForce 9400GT 6Gb RAM
+ three other Mint machines
Out of my mind - please leave a message

Post Reply

Return to “Software & Applications”