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

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

SUMMARY

Page 144 of the version 4.0 "Microsoft Excel Function Reference" 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.

REFERENCES

"Microsoft Excel Function Reference," version 4.0, pages 143-144, 267-268