Excel: Finding Exact Match with LOOKUP() FunctionsLast reviewed: November 29, 1994Article ID: Q26503 |
The information in this article applies to:
SUMMARYIn 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)),"Cannot find match",HLOOKUP(lookup_value,table_array,row_index_num)) -or- =IF(ISNA(MATCH(lookup_value,1st column table_array,0)),"Cannot find match",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 "Cannot find match" 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.
|
KBCategory: kbusage
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |