Microsoft KB Archive/95625

= Microsoft Knowledge Base =

Excel: FASTMATCH Returns #N/A Error Value When Match Exists
Last reviewed: September 12, 1996

Article ID: Q95625

The information in this article applies to:


 * Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a
 * Microsoft Excel for the Macintosh, versions 3.0, 4.0
 * Microsoft Excel for OS/2, version 3.0

SYMPTOMS
In Microsoft Excel, the FASTMATCH function should return a #N/A error value if no match is found. However, a #N/A error may also be returned even when an element exists that matches the specified value; this error message is caused by a problem in the FASTMATCH function's binary search algorithm.

STATUS
Microsoft has confirmed this to be a problem in the versions of Microsoft Excel listed above. We are researching this problem, and will post more information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION
The FASTMATCH function returns the relative position of an element in an array that matches the specified value. Use FASTMATCH when you need to search large sorted arrays.

WORKAROUND
The MATCH function can be used instead of the FASTMATCH function to obtain the relative position of an element within an array. Unlike FASTMATCH, MATCH does not require that the values in the lookup_array be in sorted order. This is because MATCH uses a slower sequential search algorithm instead of the binary search algorithm used by FASTMATCH. The syntax for the MATCH function is

=MATCH(lookup_value,lookup_array,match_type) where lookup_value is the value you use to find the value you want in the table, lookup_array is a contiguous range of cells containing possible lookup values, and match_type is the number -1, 0, or 1 that specifies how Microsoft Excel matches the lookup_value with values in the lookup_array.