[Solved] Vlookup not getting values from some cells

[Frank Sebem]

Hi all

Newbie here

I am trying to create a small calculation program using the VLookup function. I am certain that I once had it working once but for some strange reason VLookup now only selects values from some cells and omits values from other cells. Please allow me to explain...
So, I have a list of products from Cell A9 right down to Cell A29.
The prices for those products are in the corresponding cells B9 to B29.
In Cell D10 I have my Vlookup code which is =VLOOKUP(D9;A9:B29;2;0)
When I click on my arrow in Cell in D9 for some strange reason it only displays data/value from cells A9 to only Cell 23. In Cell 24 is a small snippet of information.

I am baffled because I assume that if the syntax or the code was wrong I would not got any values or the whole thing would not work. However, the thing works perfect as long as I select products from Cells A9 to A23.
Eventually, I would like there to be 100 items starting at A9 downwards. Why can't I select goods from anything after Cell 23?

Any ideas please?

PS...For the record...When I mention the snippet of information in Cell 24 I recognise that item as part of something I did input a long time ago. For further clarification as an example only, imagine this was a list of places and prices to send a telegram to.

A9 London B9 £5
A10 France B10 £50
A11 Italy B11 £20
A12 Portugal b12 £35
A13 Nice B13 £45
A14 Cannes B14 £55
A15 Ireland B15 £67
A16 Malta B16 £98
A17 Sicily B17 £4
A18 Mexico B18 £33
A19 Brazil B19 £45
A20 Chile B20 £89
A21 Peru B21 £67
A22 Belize B22 £98
A23 Qatar B23 £63
A24 United States B24 £22

You can see that all the 'A' Cells correspond with a 'B' Cell number except when I attempt to select United States in Cell 24 I get something along the lines of 'Unit'. The rest of the word is missing and also there is no option to select anything after this Cell 24.

Thanks in advance all.

[keme]

It is a lot easier to help you if you can attach the file displaying the issue. Just make sure you erase any confidential info before posting your file ; this is a public area.

Did your file by any chance origin from MS Excel (originally made in Excel, edited in Excel, or created from an Excel template)?

Have you checked the validation settings for cell D9?
Data - Validity...
Make sure the cell range specified covers all the rows you are using.

[Frank Sebem]

Aghhhh...

The machine I am using has OpenOffice on it but the file is saved as :
Microsoft Excel 97/2000/XP (.xls)

Could this be the source of the problem?

Also, I think this machine may once have had libreoffice on it.

[Frank Sebem]

Tried to save the file as .sxc file but still the same.

Thanks

[Villeroy]

menu:Tools>Options>Calc>Calculation
Regular Expressions = OFF [it is always off for xls files since Excel does not support regular expressions anyway]
Match whole cell = ON [otherwise you may get partial matches which you don't want in this case]

If =VLOOKUP(D9;A9:B29;2;0) does not match anything, the value in D9 simply is not equal to any value in A9:A29. You can test this with a simple =D9=A13 where A13 is the suspected cell which you think it should match.

[Rip Van]

I had a similar resulting problem a few years ago and solved it by moving from vlookup to index, match, which I now prefer to use. I was unable to find what was wrong with my vlookups but they were also embedded in if statements.

[Villeroy]

I had a similar resulting problem a few years ago and solved it by moving from vlookup to index, match, which I now prefer to use. I was unable to find what was wrong with my vlookups but they were also embedded in if statements.

=INDEX(area;MATCH(x;column;0);n) does exactly the same as
=VLOOKUP(x;area;n;0)
The only difference is that the former is more flexible. It can operate with disjunct ranges and return values from any other range disjunct from the search column.

And there are no "IF statements" in spreadsheets. IF is a function.

[karim.chaaban]

Hi,

I am attempting to do a project management tool for managing daily tasks, writing things as a list, then having the list manifest visually. The tool is still being developed and is unfinished.
I seem to be experiencing a similar problem to the one posted in this topic. I've included the calc file for your reference.

MIT FIRST (issue).ods

(30.33 KiB) Downloaded 122 times

The vlookup function is used in sheet "Visual R.", between columns AG & AH. I noticed that the normal expected behavior is working for some cells (highlighted in green). Those cells not working have been colored in red. Below are some attempts to solve the issue (3 attempts numbered), they could be clues for you to help me figure out this issue. In my opinion, attempt 2 reveals the most important clue, yet I am not sure how to solve the problem on my own after lots of research.

Thanks a lot for your help, much appreciated!


Attempt 1 :

I had a similar resulting problem a few years ago and solved it by moving from vlookup to index, match, which I now prefer to use. I was unable to find what was wrong with my vlookups but they were also embedded in if statements.

=INDEX(area;MATCH(x;column;0);n) does exactly the same as
=VLOOKUP(x;area;n;0)
The only difference is that the former is more flexible. It can operate with disjunct ranges and return values from any other range disjunct from the search column.

And there are no "IF statements" in spreadsheets. IF is a function.

I've tried using the match function described in the above quote, but the problem persists.
See cells highlighted in cyan (sheet "Visual R", between columns AG & AH).

Attempt 2 :

If =VLOOKUP(D9;A9:B29;2;0) does not match anything, the value in D9 simply is not equal to any value in A9:A29. You can test this with a simple =D9=A13 where A13 is the suspected cell which you think it should match.

I've set the values equal to each other (as mentioned in the above quote) and I get a FALSE, which means they do not match. I cannot figure out why though, they seem to be matching perfectly.
The test was done in sheet "Visual R.", cell Z68. If you click on the cell, you can understand which cells I am verifying. One of the cell verified is in the other sheet "MIT-F.".

Attempt 3 :

menu:Tools>Options>Calc>Calculation
Regular Expressions = OFF [it is always off for xls files since Excel does not support regular expressions anyway]
Match whole cell = ON [otherwise you may get partial matches which you don't want in this case]

This attempt uses the advise in the quote above (font in blue). I'm not sure if my options are not well set, the description is not exactly like what I have in my open office calc.
See the attached image below to understand what i mean.
In Villeroy's post, it says "Calculation". In my option version, it says "Calculate"... the other things are also differently written, so I am not sure If I selected things correctly. I do not want to change something I don't understand and mess the settings. Please let me know if something need be unselected, preferably it would be nice to understand why I should do that. I've put a rectangle next to the thing that resembles the most to the description in Villeroy's post.

Thanks a lot for your help, much appreciated!

[Villeroy]

And what is the issue? Thoo many #NA errors? They simply do not match. There is not a single value in 'MIT-F.'.$B$11:$B$35 matching the search value precisely.
Sorry, but what people try to do with spreadsheets is far beyond my understanding.

[eremmel]

I did a quick read of your question, but not 100% sure what you like to expect. However when I change the last value (0) for your vlookup(...;0) into a vlookup(...;1) in column AG I see correct values for intermediate time references from column Z.

[MrProgrammer]

I've set the values equal to each other (as mentioned in the above quote) and I get a FALSE, which means they do not match. I cannot figure out why though, they seem to be matching perfectly.

Visual R.AA62 has value 43459.1805555554 displayed as 2018-Dec-25 04:20
MIT-F.C13     has value 43459.1805555556 displayed as 2018-Dec-25 04:20

VLOOKUP(…;…;…;0) compares the cell values, not what's displayed. See [Tutorial] VLOOKUP questions and answers Q16/A16.

Cells in columns Z and AA use the formula =«CellAbove»+0.25/3/24. 0.25/3/24 is 0.00347222222222222… but some of the 2's are lost because Calc works with 15 significant digits of precision. So when you add 0.25/3/24 you lose a tiny bit. The errors accululate as you do more additions. Avoid errors due to cumulative additions by using formulas like ='MIT-F.'.G6+TIME(HOUR(AA61);MINUTE(AA61)+5;0) for AA62. This ensures you get the correct value for 2018-12-25 04:20.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[karim.chaaban]

I've set the values equal to each other (as mentioned in the above quote) and I get a FALSE, which means they do not match. I cannot figure out why though, they seem to be matching perfectly.

Visual R.AA62 has value 43459.1805555554 displayed as 2018-Dec-25 04:20
MIT-F.C13     has value 43459.1805555556 displayed as 2018-Dec-25 04:20

VLOOKUP(…;…;…;0) compares the cell values, not what's displayed. See [Tutorial] VLOOKUP questions and answers Q16/A16.

Cells in columns Z and AA use the formula =«CellAbove»+0.25/3/24. 0.25/3/24 is 0.00347222222222222… but some of the 2's are lost because Calc works with 15 significant digits of precision. So when you add 0.25/3/24 you lose a tiny bit. The errors accululate as you do more additions. Avoid errors due to cumulative additions by using formulas like ='MIT-F.'.G6+TIME(HOUR(AA61);MINUTE(AA61)+5;0) for AA62. This ensures you get the correct value for 2018-12-25 04:20.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

Hi MrProgrammer,

I was able to solve my problem with your solution, thanks a lot!
I understood from your post that cumulative addition leads to errors. You suggested a formula, but I was not able to use it directly because I kept getting the cumulative error issue.
So I modified it. Imagine this simple example :

Instead of doing :
A1 : TIME X
A2 : A1 + 5 minutes
A3 : A2 + 5 minutes
etc.
which will lead to cumulative error

To solve this issue, I simply added a column B :
B1 : 1
B2 : 2
B3 : 3
etc.

I fixed the use of cell A1 :
A1 : TIME X
A2 : $A$1 + B1 * 5 minutes (intead of : A1 + 5 minutes)
A3 : $A$1 + B2 * 5 minutes (instead of : A2 + 5 minutes)
etc.

Below, I add the calc sheet with the problem fixed (using the simple example described above).
It is based on the same original file I uploaded earlier (i.e. MIT FIRST (issue).ods), so it is easy to compare the mistake and the solution.

MIT FIRST (issue solved).ods

(30.07 KiB) Downloaded 133 times

Again, thanks a lot for your help.

And what is the issue? Thoo many #NA errors? They simply do not match. There is not a single value in 'MIT-F.'.$B$11:$B$35 matching the search value precisely.
Sorry, but what people try to do with spreadsheets is far beyond my understanding.

Villeroy, thanks for your reply.
The #NA errors are not an issue, they are normal behavior if no match is found. There are 2 values matching correctly such : "Project Management" & "Test". The values after some point stop showing up because of what seems to be a cumulative error issue, as described above in my reply to "MrProgrammer".

I originally had a 24 hours schedule with 15 minutes increment, and after changing to 5 minutes, I started encountering this issue. Sometimes, I want to go into details in my plan, and I thought this could be a nice feature to have in a project management tool. Detailing for the entire day is not the point, rather it can be done at times when judged as having a real value.

I did a quick read of your question, but not 100% sure what you like to expect. However when I change the last value (0) for your vlookup(...;0) into a vlookup(...;1) in column AG I see correct values for intermediate time references from column Z.

Thanks for your contribution. In this file :

MIT FIRST (issue solved).ods

(30.07 KiB) Downloaded 133 times

... you can look at the cells E59 to E62 which populates correctly after implementing "MrProgrammer" fix. If you are interested, I highlighted the working cells in green (Sheet "Visual R.", around Cell AC59).

I've tried your suggestion but it does not give the output I am expecting. Here is the result of what you suggested (highlighted in orange, sheet "Visual R.", starting cell AC54) :

MIT FIRST (eremmel suggestion).ods

(30.52 KiB) Downloaded 113 times

By comparing both files around the same cell (AC54), you can notice the difference.
For furthering your understanding, now look in the calc file (MIT FIRST (issue solved).ods, Sheet "Visual R.") at cells E59 to E62 which populates correctly.