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))

	A	B	C	D	E	F	G	H	I
1	Inv. #	Num	BarCode	Name	Cost		Inv. #	Num	Cost
2	101	1	111	Test1	10		102	3	30
3	101	2	222	Test2	20
4	102	1	111	Test1	10
5	102	2	222	Test2	20
6	102	3	333	Test3	30

<tbody>
</tbody>

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