Microsoft KB Archive/26503

-

{| 2.x 3.00 4.00 | 2.20 2.21 3.00 | 2.x 3.00 4.00 WINDOWS      | OS/2           | MACINTOSH kbusage The information in this article applies to:
 * width="100%"|


 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0
 * Microsoft Excel for OS/2, versions 2.2 and 3.0
 * Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0

SUMMARY
In Microsoft Excel, when you use an HLOOKUP or a VLOOKUP function to find a value in a range of sorted information, HLOOKUP or VLOOKUP returns the value of the cell. However, if the lookup_value is not found, the function returns the largest value in the range that is less than or equal to the lookup_value and does not return an error message.

To return an error message if an exact match is not found in the array, use the appropriate formula below:

=IF(ISNA(MATCH(lookup_value,1st row of table_array,0)),&quot;Cannot find     match&quot;,HLOOKUP(lookup_value,table_array,row_index_num))

-or-

=IF(ISNA(MATCH(lookup_value,1st column table_array,0)),&quot;Cannot find     match&quot;,VLOOKUP(lookup_value,table_array,col_index_num)) When you use the formulas above, if the lookup_value does not exactly match an entry in the 1st row or column of the table_array, the message &quot;Cannot find match&quot; is returned. If an exact match is found, the HLOOKUP or VLOOKUP function will be performed and will return the correct result Use HLOOKUP when your comparison values are located in a row across the top of a table and you want to search a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find.
 * }

-

Last reviewed: December 1, 1994

© 1998 Microsoft Corporation. All rights reserved. Terms of Use.