Custom VSEARCH and HSEARCH Macros to Replace VLOOKUP/HLOOKUP

 Last reviewed: November 2, 1994
Article ID: Q37777
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, 3.0

SUMMARY

The following is a function macro called VSEARCH that will take the place of VLOOKUP. It has the same syntax as VLOOKUP, but it doesn't require the data to be sorted. It will take advantage of defined names, just like VLOOKUP. HSEARCH is also available in place of HLOOKUP. More information on HSEARCH is located below the examples of calling VSEARCH:

The VSEARCH Function Macro

The following is the VSEARCH macro: 

   A1: Vsearch
   A2: =ARGUMENT("item")
   A3: =ARGUMENT("data1",8)
   A4: =ARGUMENT("col_num",1)
   A5: ="R"&ROW(data1)&"C"&COLUMN(data1)&":R"&ROW(data1)+
       ROWS(data1)-1&"C"&COLUMN(data1)
   A6: =GET.DOCUMENT(1)
   A7: =INDEX(data1,MATCH(item,TEXTREF(A6&"!"&A5),0),col_num)
   A8: =RETURN(A7)
The macro must now be defined as a function macro with the following steps:

  1. Make cell A1 (or the cell that contains the name VSEARCH) the active cell on the macro sheet.

  2. From the Formula menu, choose Define Name.

  3. Select the Function radio button or press ALT+F.

  4. Choose OK.

As long as the macro is loaded, it can be called from any sheet by using the normal procedures for calling a function macro.

Examples of How to Call Vsearch

The following examples assume that the macro sheet containing the VSEARCH macro is named VSCH.XLM:

  1. =VSCH.XLM!Vsearch("Smith",database,2)

    This example looks for the name "Smith" in the database on the active sheet and returns the value from the second column of the database, just as VLOOKUP would if the database had been sorted.

  2. =VSCH.XLM!Vsearch(A2,B1:G5,3)

    This example takes the contents of cell A2 on the active sheet and searches for it in the array B1 through G5. If it makes a match, it returns the value in the third column of that array; in this case, column D. If there is no match, #N/A is returned.

The HSEARCH Function Macro

To use HSEARCH instead of HLOOKUP, the macro is as follows: 

   B1: Hsearch
   B2: =ARGUMENT("item")
   B3: =ARGUMENT("data1",8)
   B4: =ARGUMENT("row_num",1)
   B5: ="R"&ROW(data1)&"C"&COLUMN(data1)&":R"&ROW(data1)&"C"&
       COLUMN(data1)+COLUMNS(data1)
   B6: =GET.DOCUMENT(1)
   B7: =INDEX(data1,row_num,MATCH(item,TEXTREF(B6&"!"&B5),0))
   B8: =RETURN(B7)
HSEARCH has the same parameters as VSEARCH.

KBCategory: kbusage
KBSubcategory:

Additional words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21
3.0 3.00 4.0 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 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.