Index Match using VBA

[sharky12345]

Can someone show me how to use this with VBA;

=INDEX(A2:A20,MATCH(L3,E2:E20,0))

In this case, the value of 'L3' will actually be the value of ComboBox1 on a userform and I want the result of that formula displayed in Textbox1.

Also, the sheet is sheet1.

Thanks!

 

[CalcSux78]

Function IndexMatch(ByVal rReturn As Range, ByVal vFind As Variant, ByVal rSearch As Range) As Variant
On Error Resume Next
With WorksheetFunction
    IndexMatch = .Index(rReturn, .Match(vFind, rSearch, 0))
End With
If Err.Number > 0 Then IndexMatch = "Err"
On Error GoTo 0
End Function

Worksheet Formula would be: =IndexMatch(A2:A20,L3,E2:E20)

Called from VBA:

Dim v As Variant


v = IndexMatch([A2:A20], [L3], [E2:E20])
MsgBox v

 

[Norie]

Perhaps.

Dim Res As Variant

    Res = Application.Match(ComboBox1.Value, Range("E2:E20"), 0)

    If Not IsError(Res) Then
        TextBox1.Value = Range("A2:A20").Cells(Res,1).Value
    End If

 

[sharky12345]

Cheers guys!