/docs/community?hl=en
/docs/community?hl=en
1/7/12
Original Poster
Daniel Ristic

VLOOKUP bug

Hi,

I want to report a bug in Spreadsheet when using VLOOKUP. I made screenshots to show the bug.

Here is how it is supposed to work : I use VLOOKUP on entire columns (G:H in this case) to add values in the column C when values of A and G match. 

It works but I omit the last argument (sort order), it gives me an error :

Then, if I do the exact same thing on a Spreadshit created with the French version of the interface, it doesn't work, regardless of using the sort order :

or not :

Cheers.

Community content may not be verified or up-to-date. Learn more.
All Replies (4)
anandvarma
1/7/12
anandvarma
Dear danielr,

the vlookup function argument is as follows:

VLOOKUP(search_criterion, array, index, sort_order) 

this vlookup function is sometimes a volatile function ( some times it will work some times it is now)

in the first screen shot it is working properly because all the arguments are complete. 

it may be for the following reasons

data in the first column of array is not stored as text values. In this case, VLOOKUP may give an incorrect or unexpected value 

array has to be sorted and there should not be any duplicates

anand varma

1/7/12
Original Poster
Daniel Ristic
So you're telling me that it's not a bug, the function is volatile so I shouldn't expect it to give right results or to work at all unless the columns that match are both text, the arguments are all set, the lines are sorted and there's no duplicate?

I think you're right because I managed to have an error with the english version of the spreadsheet and made the french one work so it was not language related.

I guess I'll have to find a better way to mimic SQL JOIN because this one is just not reliable enough.

Thanks for the help!

Cheers.
AD:AM
1/7/12
AD:AM
The optional fourth argument in VLOOKUP (and HLOOKUP) determines two things: whether an exact match is required or not, and whether the lookup array needs to be sorted, and if so, in what order.

If the fourth argument is 1, an exact match is not required (it will search for the largest value that is less than or equal to the search value), and the lookup array must be sorted in ascending order, otherwise unexpected results might occur.  If the fourth argument is omitted, it will default to this value (1).

If the fourth argument is -1, an exact match is not required (it will search for the smallest value that is greater than or equal to the search value), and the lookup array must be sorted in descending order, otherwise unexpected results might occur.

If the fourth argument is 0, an exact match is required, and the lookup array does not need to be sorted.

The first two options use a binary search algorithm, hence requiring sorted arrays.

That being said, in GSheets I would argue that there is a degree of "bugginess" associated with VLOOKUP with non-exact matches, in that the "unexpected results" are different to the "unexpected results" that you get in, say, Excel. :-/

Bottom line, if you are only wanting to look for exact matches, always use zero for the fourth argument (+danielr, I think I understand from your most recent post that it is working in the French version now?).

1/8/12
Original Poster
Daniel Ristic

Thanks for all these clarifications, it's very useful.

It's now working with the french version, I just had to re-enter the formula and this time it worked on my test spreadsheet.

Cheers.

Were these replies helpful?
How can we improve them?
 
This question is locked and replying has been disabled. Still have questions? Ask the Help Community.

Badges

Some community members might have badges that indicate their identity or level of participation in a community.

 
Google Employee — Google product team members and community managers
 
Community Specialist — Google partners who help ensure the quality of community content
 
Platinum Product Expert — Community members with advanced product knowledge who help other Google users and Product Experts
 
Gold Product Expert — Community members with in-depth product knowledge who help other Google users by answering questions
 
Silver Product Expert — Community members with intermediate product knowledge who help other Google users by answering questions
 
Product Expert Alumni — Former Product Experts who are no longer members of the program
Community content may not be verified or up-to-date. Learn more.

Levels

Member levels indicate a user's level of participation in a forum. The greater the participation, the higher the level. Everyone starts at level 1 and can rise to level 10. These activities can increase your level in a forum:

  • Post an answer.
  • Having your answer selected as the best answer.
  • Having your post rated as helpful.
  • Vote up a post.
  • Correctly mark a topic or post as abuse.

Having a post marked and removed as abuse will slow a user's advance in levels.

View profile in forum?

To view this member's profile, you need to leave the current Help page.

Report abuse in forum?

This comment originated in the Google Product Forum. To report abuse, you need to leave the current Help page.

Reply in forum?

This comment originated in the Google Product Forum. To reply, you need to leave the current Help page.