XL: Range_Lookup Argument in VLOOKUP() Finds Exact Match

 Last reviewed: February 12, 1998
Article ID: Q108639
The information in this article applies to:
  • Microsoft Excel 98 Macintosh Edition
  • Microsoft Excel for Windows, versions 5.0, 5.0c
  • Microsoft Excel for the Macintosh, versions 5.0, 5.0a
  • Microsoft Excel for Windows NT, version 5.0
  • Microsoft Excel for Windows 95, version 7.0
  • Microsoft Excel 97 for Windows

SUMMARY

In Microsoft Excel versions 5.0 and later, the VLOOKUP() and HLOOKUP() functions contain an argument called range_lookup. The syntax of these functions are different than in earlier versions and are defined as follows 

   =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
where: 

   Argument     Definition of argument
   ---------------------------------------------------------------------

   lookup_value The value to be found in the first column of the array.

   table_array  The table of information in which data is looked up.

   col_index    The column number in the table_array for which the
                matching value should be returned.

   range_lookup This is a new argument used in Microsoft Excel versions
                5.0 and later. It is a logical value that specifies whether
                you want to find an exact match or an approximate match.
                If TRUE or omitted, an approximate match is returned; in
                other words, if an exact match is not found, the next
                largest value that is less than the lookup_value is
                returned. If FALSE, VLOOKUP() will find an exact match.
                If an exact match is not found, the #N/A error value is
                returned.
NOTE: If range_lookup is TRUE or omitted (that is, an approximate match), the values in the first column of table_array must be sorted in ascending order. If range_lookup is FALSE (that is, an exact match), the table_array does not need to be sorted.

=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

where: 

   Argument     Definition of argument
   ---------------------------------------------------------------------

   lookup_value The value to be found in the first column of the array.

   table_array  The table of information in which data is looked up.

   row_index    The row number in the table_array for which the
                matching value should be returned.

   range_lookup This is a new argument used in Microsoft Excel versions
                5.0 and later. It is a logical value that specifies whether
                you want to find an exact match or an approximate match.
                If TRUE or omitted, an approximate match is returned; in
                other words, if an exact match is not found, the next
                largest value that is less than the lookup_value is
                returned. If FALSE, VLOOKUP() will find an exact match.
                If an exact match is not found, the #N/A error value is
                returned.
NOTE: If range_lookup is TRUE or omitted (that is, an approximate match), the values in the first row of table_array must be sorted in ascending order. If range_lookup is FALSE (that is, an exact match), the table_array does not need to be sorted.

In earlier versions of Microsoft Excel, the INDEX() and MATCH() functions must be used to find an exact match in the table_array.

For additional information about using the VLOOKUP function in earlier versions of Microsoft Excel, please see the following article in the Microsoft Knowledge Base: 

   ARTICLE-ID: Q77114
   TITLE     : Performing a Lookup with Unsorted Data in Excel

REFERENCES

For more information about VLOOKUP(), click the Search button in Help and type: 

   compare lookup return

Additional query words: 5.00 5.00a 5.00c 7.00 8.00 97 XL97
Keywords : xlformula xlhelp
Version : WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0,5.0a,98
Platform : MACINTOSH WINDOWS

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: February 12, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.