Help with UDF to search through a table

[liang.yuwei]

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

--------------------------------

 

[Joel]

Function MultiMatch(rg As Range, _
ParamArray index() As Variant) As Number

For Indexcount = 0 To UBound(Index())

Do While Not (rg.Value = Index(Indexcount))

rg = rg.Offset(1, 0)

Loop

rg = rg.Offset(0, 1)
Next Indexcount

MultiMatch = rg.Value

End Function