Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with UDF to search through a table
hi all
I am trying to write a UDF that can travel through a sorted table and find a value based on 9 inputs. For example. say the UDF name is MultiMatch. I will type in Page 2 Cell A1 " =Multimatch('Page1'!A1, AA1, AB1, AC1, AD1, AE1, AF1, AG1, AH1) " all the inputs are cell addresses. MultiMatch will start at 'Page1'!A1, and travel down column A until it finds a cell with value equal to AA1 (the second input of MultiMatch), in this case A30. It will then shift to the next column, B30, and travel down column B until it arrives at a cell equal to cell AB1, in this case B40. It then shift right yet again to C40, then travel down column C to find cell with same value as AC1, which is C50, and then shift right to column E yet again, and so on and so forth, until the final column H was arrived where a cell has the same value as AH1, say cell H100. Multimatch last returns the numerical value of the next cell to the right of H100, which was G100 in our example. I have written the below UDF, but I can't seem to make it work. Can anyone look at this and help me out? I really don't want to use concatenate all the keys then use VLookup. I know it works but its not the way I want to do things for this model. Thank you all -------------------------------- Function MultiMatch(rg As Range, index1 As String, index2 As String, index3 As String, index4 As String, index5 As String, index6 As String, index7 As String, index8 As String) As Number Do While Not (rg.Value = index1) rg = rg.Offset(1, 0) Loop rg = rg.Offset(0, 1) Do While Not (rg.Value = index2) rg = rg.Offset(1, 0) Loop rg = rg.Offset(0, 1) Do While Not (rg.Value = index3) rg = rg.Offset(1, 0) Loop rg = rg.Offset(0, 1) Do While Not (rg.Value = index4) rg = rg.Offset(1, 0) Loop rg = rg.Offset(0, 1) Do While Not (rg.Value = index5) rg = rg.Offset(1, 0) Loop rg = rg.Offset(0, 1) Do While Not (rg.Value = index6) rg = rg.Offset(1, 0) Loop rg = rg.Offset(0, 1) Do While Not (rg.Value = index7) rg = rg.Offset(1, 0) Loop rg = rg.Offset(0, 1) Do While Not (rg.Value = index8) rg = rg.Offset(1, 0) Loop rg = rg.Offset(0, 1) MultiMatch = rg.Value End Function -------------------------------- |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with UDF to search through a table
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to search a column that is not the first of the table | Excel Worksheet Functions | |||
need to search a table such as distance table in road maps. | Excel Discussion (Misc queries) | |||
How do i build a search table in excel | Excel Worksheet Functions | |||
search from a data table | Excel Discussion (Misc queries) | |||
search value in a table and sum another column | Excel Worksheet Functions |