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

[Portreve]

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!

[AndyMH]

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.

[lsemmens]

Silly question. Why persist with a spreadsheet for database functionality when you have a perfectly good database as part of the Office Suite?

[Portreve]

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.

[lsemmens]

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.

[deepakdeshp]

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.

[AndyMH]

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

Range-lookup set FALSE, data not sorted, correct answer

I assume LO calc will be working the same way as excel.

[rene]

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...

[AndyMH]

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.

[rene]

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...

[Portreve]

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.

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:

[lsemmens]

I'm guessing that you are a bit of a Trekkie, along with smatterings for Dr Who and a few other popular shows.
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.

[AndyMH]

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.

[Portreve]

I'm guessing that you are a bit of a Trekkie, along with smatterings for Dr Who and a few other popular shows.

You missed the Babylon 5 references!

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.

[lsemmens]

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.