WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Application.Match

VBA Application.Match

Excel VBA Application.Match

One of the best things about VBA is it has its own function and also it will allow us to access all the worksheet functions under the “Worksheet Function” class. You must have already used MATCH function as a worksheet function, but it is not a VBA function, so we need to access under worksheet function class. In this article, we will show you how to use the MATCH function in VBA using the Application method.

VBA Application.Match

Quick Recap of MATCH Function

MATCH is a lookup function that looks for the position of the lookup value in the mentioned lookup array. For example, look at the below image of the data.

In the above data, we have months from A2 to A6, and if we want to know in which place “Mar” month occurs, then we can use the MATCH function.

Below is the syntax of the MATCH function.

MATCH (Lookup Value, Lookup Array, [Match Type])
  • Lookup Value: For which value we are looking for the position in the lookup array.
  • Lookup Array: In which array we are looking for the position of the lookup value.
  • [Match Type]: For this, we can provide three arguments.
  1. 1 = Less Than
  2. 0 = Exact Match
  3. -1 = Greater Than

Most of the time, we use only “0 Exact Match”.

You can download this VBA Application.Match Excel Template here – VBA Application.Match Excel Template

How to Use Application.Match Function in VBA?

Example #1

Look at the below data in excel.

VBA Application match Example 1

From the above data, we need to find the position of the “Mar” month in the range of cells from A2 to A6. Since we need results in D2 cells straight away start the code as Range(“D2”).Value =.

VBA Application match Example 1-1

To arrive value in D2 cell, we need to use MATCH worksheet function, so to access this first, we need to access the APPLICATION object and then WORKSHEET FUNCTION object.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

VBA Application match Example 1-2

Enter dot to see a list of worksheet functions.

VBA Application match Example 1-3

Choose “Match” from the list.

VBA Application match Example 1-4

One of the problems in VBA while using worksheet functions is we do not see exact syntax as we see with worksheet function. This is one of the reasons we have explained the syntax at the beginning only.

So, the first argument is lookup value, i.e. for which value we are looking to find the place. In this case, we are looking to find the place for “Mar” which is in the C2 cell, so supply the cell reference.

Example 1-5

The next argument is lookup array, i.e. in which range we are looking for the position of a lookup value, for this supply cells from A2 to A6.

Example 1-6

The last argument will be an exact match, so supply 0.

Code:

Sub Match_Example1()
Range("D2").Value = Application.WorksheetFunction.Match(Range("C2").Value, Range("A2:A6"), 0)
End Sub

Ok, we are done with the formula.

Run the code through the F5 key and see what we get.

VBA Application match Example 1-7

So we have got the result as 3 because the value “Mar” is in the place of 3rd position in the range A2 to A6.

This MATCH function can provide the position of the lookup value. However, MATCH function largely used with the VLOOKUP function to supply the column index number based on the column header.

Example #2

Now we will see how to use MATCH as a supporting function for the VLOOKUP function.

Look at the below data.

Data Example 2

In the above table, we are looking at the year 2018 “Feb” month sales, so we need to use the VLOOKUP function. VLOOKUP is also a worksheet function, so access this like how we have accessed the MATCH function.

Example 2-1

Lookup Value will be G2 cell, so supply cell address.

Code:

Sub Match_Example2()
Range("H2").Value = Application.WorksheetFunction.VLookup(Range("G2").Value,
End Sub

Table Array will be from A2 to D6.

Code:

Sub Match_Example2()
Range("H2").Value = Application.WorksheetFunction.VLookup(Range("G2").Value,Range("A2:D6"),
End Sub

Now we need to provide from which column of the table array we are looking for the result, so this will be from the 3rd column. Instead of supplying the column number as 3, let’s use the MATCH function.

Code:

Sub Match_Example2()
Range("H2").Value = Application.WorksheetFunction.VLookup(Range("G2").Value, Range("A2:D6"), Application.WorksheetFunction.Match(Range("H1").Value, Range("A1:D1"), 0), 0)
End Sub

So MATCH function provides the column number from the range A1 to D1 for the month “Feb”. Let’s run the code and see how it gets column numbers automatically.

VBA Application match Example 2-2

There you go we have a result from column number 2 thanks to automatic column number supply from MATCH function.

Things to Remember

  • MATCH function looks for the position of the lookup value in the selected array table.
  • The MATCH function is mainly used with the VLOOKUP function to supply the column index number automatically using column heading.
  • The MATCH function is available as a worksheet function in VBA.

Recommended Articles

This has been a guide to VBA Application.Match. Here we discuss how to use the MATCH function in VBA using the Application method along with practical examples and downloadable excel template. you may learn more from the following articles –

  • VBA LOOKUP
  • VLookup in VBA Excel
  • VBA XLUP
  • VBA Solver
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Application.Match Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More