Make UDF based on multiple Match-criteria

[MortenJ]

Has this formula made of Index and Match:

=INDEX(Range,Match(1, (C:C=criteria1)*(D:D=criteria2)*(B:B=criteria3),0),5)

The '5' represents the column number in Range that holds the cell with the value that should return as result of the formula.

Basically the data table has 5 columns; #1 not used, #2 criteria3, #3 criteria1, #4 criteria2, #5 the value

How do I convert this into an UDF?

 

[MortenJ]

sorry - the formula is an array formula:

{=INDEX(Range, MATCH(1, (C:C=critera1)*(D:D=criteria2)*B:B=criteria3),0),5)}

Cannot make VBA to do the array calculation. It calculates as if the array brackets are not included

 

[DanteAmor]

Try this


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Result</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >a2</td><td >b2</td><td >criteria1</td><td >d2</td><td >e2</td><td >e3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >a3</td><td >criteria3</td><td >criteria1</td><td >criteria2</td><td >e3</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >a4</td><td >b4</td><td >criteria1</td><td >criteria2</td><td >e4</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Function</td></tr><tr><td >F2</td><td >=mult_criteria(A2:E4,C2:C4,"criteria1",D2:D4,"criteria2",B2:B4,"criteria3",5)</td></tr></table></td></tr></table>

Function mult_criteria(arry As Range, r1 As Range, crit1 As String, r2 As Range, crit2 As String, _
                                      r3 As Range, crit3 As String, col_num As Long)
  mult_criteria = Evaluate("=index(" & arry.Address & ",MATCH(1,(" & _
        r1.Address & "=""" & crit1 & """)*(" & r2.Address & "=""" & crit2 & """)*(" & _
        r3.Address & "=""" & crit3 & """),0)," & col_num & ")")
End Function