user defined function to return a string value based on two column matches

Hi there,

I am trying my hand at creating a user defined function. I have done a good amount of macro programming before but never needed to look at UDFs... until now.

I have a sheet which does what I want it to do (if I use array formulas) but this slows it right down to a crawl so I want a slicker solution. I also want to avoid pivots as I will be writing code to produce these sheets automatically and I don't want to go anywhere near combining pivots and vba!

Essentially I have a set of observation records where I have each person making one or more individual actions. For each action a status is allocated (in this case S, W or B).

I need my function to be able to tell me what the status is for each person, for a specific action.

Not sure what I am doing wrong but if anyone out there has any ideas I am all ears!
UDF.xlsb
I have attached a basic example of what I am trying to do. function code is as follows.....

Function DoubleMatch(Parameter1 As String, Parameter1_column As Range, Parameter2 As String, _
Parameter2_column As Range, Result_column As Range)

Dim i As Long
Dim result As String

For i = 1 To Parameter1_column.Count
If Parameter1_column.Cells(i, 1).Value = Parameter1 Then
If Parameter2_column.Cells(i, 1).Value = Parameter2 Then
result = Result_column.Cells(i, 1).Value
End If
End If
Next

End Function

thanks

Ed

Ed - you just need to assign the result to your formula.

Ok, stupid question coming up.... what do you mean? How do I do that?

Well you have to tell Excel that the result of your formula is the value it should assign to DoubleMatch, i.e. add this at the end

Please Login or Register  to view this content.

wow, close but no cigar for me!

works a treat now. you're a legend.

thanks

Ed