WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Match Excel Function

Match Excel Function

By Tanuj KumarTanuj Kumar | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

MATCH in Excel

Match function in excel is used to find a position of the reference cell in a given range of cells, the reference is a value which is searched from a range of cells, and the position is the first position which is found for the value, the method to use this formula is as follows =MATCH( Value to be searched, Table, and exact or approx. match {0 or 1}).

Syntax

The MATCH formula is as follows

Match Formula

Explanation

MATCH Formula has three-parameter two (lookup_value,lookup_array) are compulsory parameters and one (match_type) is optional.

Compulsory Parameter:

  • lookup_value: lookup value is a value that you want to search in the lookup array. Suppose you want to search a word in the dictionary, then that particular word will be your lookup value, and the dictionary will be your lookup array.
  • lookup_array: lookup_array is the set of data where the lookup value is located, and your search applied in lookup array.

Optional Parameter:

  • [match_type]: :match_type is limited to -1, 0, or 1, depending on the type of ‘MATCH’.
    • It will be 0 for the first exact match lookup value.
    • 1 for the largest value < or = toour ‘lookup_value’.
    • And -1 for the smallest value that is > or = toour ‘lookup_value’.

How to use MATCH Function in Excel? (with Examples)

Let us understand the working of Excel Match Formula by some Excel examples.

You can download this MATCH Function Excel Template here – MATCH Function Excel Template

Example #1

Consider a MATCH Excel example for exact match value. Suppose you have a data table with S.N., Name, and Dept. name and you have to find out the position of an employee whose name is ‘Tanuj’ then you can use MATCH Formula in excel with 0 match type to return the exact position of ‘Tanuj’ in the table array.

MATCH Function Example 1

= MATCH(F4,$B$4:$B$13,0)  the output is 1.

Example #2

Consider the MATCH Excel example for approximate match value. Suppose you have a list of values wherein you need to find out the approximate position of any value; here, we have searched for ‘525’, and it will return an approximate position for it.

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

MATCH Function Example 2

= MATCH(E19,B19:B28,1) output will be 5.

Example #3

Match support (? & *) wildcard characters in excel search in lookup value. You can understand MATCH Formula in Excel by using the MATCH Excel example.

MATCH Function Example 3

=MATCH(“*”&E33,$B$33:$B$42,0) the output will be 5.

Example 4

MATCH in Excel with index function is used to lookup value in the table from right to left.

Suppose you have to find out the S.N. of any employee by their name, then you can achieve it by using a combination of excel match index function.

Example 4

=INDEX(B46:D56,MATCH(G48,C46:C56,0),1) an output will be 1.

Things to Remember

  • The match is not case-sensitive. It considers tanuj and Tanuj as the same value means it does not distinguish between the lower case and upper case.
  • MATCH in Excel returns the #N/A error if there is no matching value is found in the lookup_array.

Example 5

  • The MATCH function returns the position value of the lookup value in the lookup_array, not the value itself.
  • If there are multiple match values in lookup_array, then it will return the first exact match value.
  • If match_type is 0 and lookup_value is text, then this function supports the use of wildcard characters question mark (?) and asterisk (*) in lookup_value.

Recommended Articles

This has been a guide to Excel MATCH Function. Here we discuss the MATCH Formula in excel and how to use it with INDEX function along with practical examples and a downloadable excel template. You may also have a look at these other lookup and reference functions in excel.

  • Excel Match Multiple Criteria
  • Excel Mathematical Function
  • INDEX Formula
  • VBA Match
4 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 MATCH Function Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More