VBA Match versus .Worksheetfunction Match - Excel VBA Is Fun!

In this video lesson, we review the differences between the VBA native function "Match" versus the worksheet function, also available this way in VBA. There are some differences, but they usually do the same thing. In this instance, the VBA native function handles errors differently though, Check out how we resolve this issue!

Fantastic Developer Tools:

[excelisfun]

You are so awesome for answering questions and making Excel VBA videos! You rock, Dan!

[krn]

Dan, the zero at the end of the MATCH formula tells the function you want an exact match. Great job.

[zarkotripunovic]

I had same problem. I used "On Error Resume Next" and it wasn't enough, but Your proposal (Y=empty) solved the problem. Thanks

[marindavanderwalt]

thank you for the solution!! I spent hours looking for a solution, yours is the only one that works!

[f-vall]

I would just say that sometimes is important to add the On Error GoTo 0 command after the If. Otherwise it can cause troubles in the code in case an unwanted error shows.

[vallorahn]

An idea:

On Error Goto 100
For X = 2 To LR1
y = Application.WorksheetFunction.Match(ST1.Cells(X, 1), ST2.Range("A:A"), 0): Goto 200
100
ST1.Cells(X, 1).Rezise(1, 2).Copy Destination:=ST3.Cells(Z, 1): Z = Z + 1
200
Next X

[TanvirMostofa]

Dear sir,
I want to know how to solution the three criteria index match function in Visual basic excel file ? Can i shear a excel file this related problem.

[Encarta]

Hey Dan, awesome work. My first time seeing your video. Is it possible to enter also the excel formula ceiling into the existing match function?

[ExcelVbaIsFun]

Thats great, glad it helped. Very welcome!

[AlinGaoChannel]

great video. thanks a lot for your help.

[adameu]

Please can anyone help me.
I have in sheet1 B1=PW, B2=MB, B3=VC and in Sheet2 I have D3=PW, D4=MB, D5=VC.
 I created the button macro to run when I select B1 in Sheet1 and I press the macro button to go automaticly to sheet2 to  D3, the match name for PW...but when I select B2 in sheet1 and I press the macro button it is going to sheet2 and select D3 again instead of D4 which is the match to MB in sheet1..And again if I select B3 in sheet1 and I press the macro button I go to Sheet2 and select D3 again instead of D5. I dont know how to write the VBA to match when I select a cell in Sheet1 and press the macro button to go in Sheet2 and to be selected automaticly the matched cell with the name as same in cell in Sheet1...Thank you all

[TanvirMostofa]

Dear sir,
I am facing some problems with the index match function (Vba macro Excel)
But i can not explain.I want to share an excel file to describe . I need help. Please help me . If you want to help me, please send your mail address to share the file.