Custom VSEARCH and HSEARCH Macros to Replace VLOOKUP/HLOOKUPLast reviewed: November 2, 1994 |
The information in this article applies to:
SUMMARYThe 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 MacroThe 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:
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 VsearchThe following examples assume that the macro sheet containing the VSEARCH macro is named VSCH.XLM:
The HSEARCH Function MacroTo 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 Last reviewed: November 2, 1994 |