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: