Microsoft KB Archive/91725

{|
 * width="100%"|

Excel: If Match Type Is 0 (zero) FASTMATCH Returns #VALUE

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, version 4.0
 * Microsoft Excel for the Macintosh, version 4.0

-

SUMMARY
Page 144 of the version 4.0 &quot;Microsoft Excel Function Reference&quot; indicates that 0 (zero) can be used in the type_of_match argument in the FASTMATCH function. This information is incorrect. If you use 0 in FASTMATCH, Excel returns the #VALUE error.

MORE INFORMATION
The FASTMATCH function uses a binary search algorithm which works only with sorted arrays.

Use the following guide for the type_of_match argument:


 * If your lookup array is sorted in ascending order (lowest to highest, A-Z), use a 1 for type_of_match (or omit it).
 * If your lookup array is sorted in descending order (highest to lowest, Z-A), use a -1 in type_of_match.
 * If your lookup array is not sorted, or if you want to return a positive value only if an exact match is found, use MATCH instead of FASTMATCH, and use 0 (zero) for the match_type argument.

Note: Your lookup_array does not have to be in sorted order if you use 0 (zero) in the match_type argument for the MATCH function.