| Summary: | Vlookup > Returns the cell above the actual data | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | kyle sager <kylesager1> |
| Component: | Calc | Assignee: | Not Assigned <libreoffice-bugs> |
| Status: | RESOLVED NOTABUG | ||
| Severity: | normal | CC: | frob, miguelangelrv |
| Priority: | medium | ||
| Version: | 3.4.4 release | ||
| Hardware: | x86-64 (AMD64) | ||
| OS: | Linux (All) | ||
| Whiteboard: | |||
| Crash report or crash signature: | Regression By: | ||
|
Description
kyle sager
2012-06-22 20:07:44 UTC
I just notices: For the entire data set, In the lookup table there is a space character at the end of every lookup key - column 1. I.E. column one is: column 1 column 2 Column 3 United States[space] 309975000 etc..etc on the first few, if I remove the spaces at the end of data lines, lookup appears to function correctly. Will try all and report back. Yup...that did the trick. If there is a space at the end of the field in every record in the lookup column, then the value returned is for the row above. Get rid of the space and it works. I did not test other columns with the bug. This may not be worth chasing. Your far more advanced programming minds can determine. Could you please provide a sample file or at least a formula your are using? Dear Bug Submitter, This bug has been in NEEDINFO status with no change for at least 6 months. Please provide the requested information as soon as possible and mark the bug as UNCONFIRMED. Due to regular bug tracker maintenance, if the bug is still in NEEDINFO status with no change in 30 days the QA team will close the bug as INVALID due to lack of needed information. For more information about our NEEDINFO policy please read the wiki located here: https://wiki.documentfoundation.org/QA/FDO/NEEDINFO If you have already provided the requested information, please mark the bug as UNCONFIRMED so that the QA team knows that the bug is ready to be confirmed. Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team Sorry I left this go so long. Widowed dad juggling things!
I just successfully duplicated this bug(?). I just realized this if probably not a bug if vlookup is intended to return the first line *before* a value if there is not an exact match.
Here is an example table:
A B C
1 A100002 100002 A100002
2 A100003 100003 A100003
3 A100004 100004 A100004
4 A100005 100005 A100005
5 A100006 100006 A100006
The problem I percieved was if there were spaces after data in the first column then the following happened
The formula =VLOOKUP("A100003",A1:C5,3)
returns A100002
But the formula =VLOOKUP("A100003 ",A1:C5,3)
with space after A100003 returns A100003
I actually think this is intentional because lookup key, when it has a trailing space, is not an exact match. I will leave this "notabug." and apologized for the confusion.
Thank you for the update. Best wishes ~Joel Have you looked for the function in the help:
"
Syntax
=VLOOKUP(SearchCriterion; Array; Index; SortOrder)
---------
Vertical search with reference to adjacent cells to the right. This function checks if a specific value is contained in the first column of an array. The function then returns the value in the same row of the column named by Index. If the SortOrder parameter is omitted or set to TRUE or one, it is assumed that the data is sorted in ascending order. In this case, if the exact SearchCriterion is not found, the last value that is smaller than the criterion will be returned. If SortOrder is set to FALSE or zero, an exact match must be found, otherwise the error Error: Value Not Available will be the result. Thus with a value of zero the data does not need to be sorted in ascending order.
"
|