Using Index function in userform

[NDMDRB]

Hello,

Please I need your help

I'm trying to find a value reference to two columns, I'v tried it in Excel worksheet, it works fine, but I'm not able to use it in VBA

Here is the function that I tested in Excel worksheet in range (I2) and works fine

=INDEX(E:E,MATCH(1,(A:A=G2)*(B:B=H2),0))


[TABLE="width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Inv. #[/TD]
[TD]Num[/TD]
[TD]BarCode[/TD]
[TD]Name[/TD]
[TD]Cost[/TD]
[TD][/TD]
[TD]Inv. #[/TD]
[TD]Num[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]101[/TD]
[TD]1[/TD]
[TD]111[/TD]
[TD]Test1[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]102[/TD]
[TD]3[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]101[/TD]
[TD]2[/TD]
[TD]222[/TD]
[TD]Test2[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]102[/TD]
[TD]1[/TD]
[TD]111[/TD]
[TD]Test1[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]102[/TD]
[TD]2[/TD]
[TD]222[/TD]
[TD]Test2[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]102[/TD]
[TD]3[/TD]
[TD]333[/TD]
[TD]Test3[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I tried to use it in my userform but I get an error

Here is the code I tried

Dim ws As Worksheet
Set ws = Sheet6


Set findvalue = Application.WorksheetFunction.Index(ws.Range("E:E"), Match(1, (ws.Range("A:A") = Me.List_Sales.Value) * (ws.Range("B:B") = Me.List_Details.Value), 0))
Me.txtReturn = findvalue

Can someone please help me how make it correct?

 

[Fluff]

Don't think you can use an array formula like that, try


FindValue = ws.Evaluate("Index(E:E, match(1, (A:A = " & Val(Me.List_Sales.Value) & ") * (B:B =" & Val(Me.List_Details.Value) & "), 0))")

 

[NDMDRB]

Don't think you can use an array formula like that, try


FindValue = ws.Evaluate("Index(E:E, match(1, (A:A = " & Val(Me.List_Sales.Value) & ") * (B:B =" & Val(Me.List_Details.Value) & "), 0))")

Amazing, thanks a lot dear, it works very well

 

[Fluff]

My pleasure & thanks for the feedback