/docs/community?hl=en
/docs/community?hl=en
7/22/12
Original Poster
Bas Braams

Trouble with searching and matching; functions SORT, MATCH, HLOOKUP and VLOOKUP

Here is a sheet that shows some issues with the searching and matching functions in Google Spreadsheets: functions SORT, MATCH, HLOOKUP and VLOOKUP. The sheet is accessible to anyone with the link. I continue to edit it and it needs some more work indeed.


The examples in the sheet address the following issues.

SORT moves blank fields to the back for either direction of sort
In the standard lexicographical order the empty string comes first, but Google Spreadsheets SORT moves blank fields to the back for any direction of sort. This creates unpleasant surprises if SORT is used to prepare an input to MATCH or to other such functions.

MATCH function returns wrong results for sorted data in a row
The function MATCH(key,array) without 3rd argument is expected to use binary search to locate the key in a sorted array and return the relative position of the last instance in the array that is less than or equal to the key. The function seems to fail dramatically if the array is a row.

MATCH function returns wrong results for sorted data in a column
When the MATCH function is applied to a sorted column array then apparently the binary search may go outside the array and if a match is found there then that is the returned result.

MATCH function returns wrong results for data in a free format column
When the MATCH function is applied to a sorted column array in free format (as in A:A) and the key is not found then results are erratic.

HLOOKUP fails for rows of length 1 and VLOOKUP fails for columns of length 1
The search functions HLOOKUP and VLOOKUP are closely related to MATCH as applied to a row or a column respectively. Instead of returning the index of the key in the search array, as MATCH does, they return the value of an element at that position in another row (HLOOKUP) or column (VLOOKUP) of the same array. It is found that the functions cannot be applied if the search array has length 1.

HLOOKUP for sorted arrays fails when searching for 0 in a row of all 0
An oddball error for HLOOKUP that can be associated with an error in MATCH described earlier in this Section.

VLOOKUP for sorted arrays has multiple unexpected failure conditions
Several errors in HLOOKUP may be associated with corresponding errors in the MATCH function for the case of vertical arrays.

VLOOKUP for sorted array returns wrong results when there are blanks in the array
The behavior of VLOOKUP for a sorted array when there are blank fields in the array is chaotic even if the array has been sorted by the SORT function and the blank fields therefore occur at the end.

This sheet on searching and matching functions is one of several such sheets that are collected together here:


Comments welcome!
Community content may not be verified or up-to-date. Learn more.
All Replies (2)
11/3/12
Original Poster
Bas Braams
All issues raised in the OP here are still issues on 2012-11-03. Some programmers would be embarrassed to be called out on having messed up a binary search, but this seems to be not the case for the GDocs technical team and their management. I refer also to [1-2].

[1] (2012-05-06) Errors and other issues with statistical and mathematical functions in GSheets

[2] (2012-09-26) Errors in the spreadsheets program
6/15/13
Original Poster
Bas Braams
The behavior of the HLOOKUP, VLOOKUP and MATCH functions is unchanged on 2013-06-14 from what was reported here previously. There has been no change either in the other errors in the Spreadsheets program inventorised under refs [1] and [2] of my posting here of 2012-11-03. It leads to my conjecture [3] that there has been a decision within the Google Apps team that the Docs text editor and spreadsheets programs will not be developed further.

[3] (2013-04-14) Prediction: Docs text editor and spreadsheets to be frozen and abandoned in favor of Quickoffice and other products
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.