Microsoft KB Archive/37777

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: Make cell A1 (or the cell that contains the name VSEARCH) the active cell on the macro sheet. From the Formula menu, choose Define Name. Select the Function radio button or press ALT+F. 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: =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. =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 Last reviewed: November 2, 1994 © 1998 Microsoft Corporation. All rights reserved. Terms of Use.