Excel: Finding Exact Match with LOOKUP() Functions

 Last reviewed: November 29, 1994
Article ID: Q26503

The information in this article applies to:

  • 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)),"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
KBSubcategory:

Additional words: 1.00 1.03 1.04 1.06 1.50 2.20 3.00 4.00



THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 29, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.