MATCH Function in Excel – Formula Explained with Examples

Home/Excel Formulas/MATCH Function in Excel – Formula Explained with Examples

MATCH function: Explained with Examples

MATCH function is just opposite to Index Function. Index returns a match value for a given index from the range. Where as Match function returns the index for the specific value from the range.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Ultimate Project Management Template
Excel Template

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


What is the use of MATCH function?

MATCH Function in Excel searches for a value in an array and returns the relative position of that item

What is the syntax of MATCH function?

MATCH Function in Excel - Snt

MATCH( value, array,

[match_type] )

value: The value to be searched in the the array.
array: The range or a range name containing the table of data.
[match_type]: Type of match that the function to be performed.
1 = Closest match – largest value that is less than or equal to value (data to be sorted ascending) [Default] 0 = Exact matches are returned
-1 = Closest match – smallest value that is greater than or equal to value (data to be sorted descending)

MATCH Function in Excel- Example

MATCH Function in Excel – Examples

Example 1: Searching for 1008 and found at 2nd place

=MATCH(1008,A5:A8,0) search for 1008 in the range A5:A8 and returns its matched index 2 (it found 1008 at second place in the range A5:A8).

Example 2: Searching for 1005 and not found, returned #NA (since match type is 0 i.e; exact match)

=MATCH(1005,A5:A8,0) search for 1005 in the range A5:A8 and returns its matched index #NA (it does not found 1005 in the range A5:A8).

Example 3: Searching for 1022, returned 3 (since match type is 1 i.e; largest value that is less than or equal to value)

=MATCH(1022,A5:A8,1) search for 1022 in the range A5:A8 and returns its matched index 3 (it does not found 1022 in the range A5:A8, and returns nearest matched value (1020) index as we specified match type as 1).

Example 4: Searching for 1022, returned 3 (since match type is 1 is by default)

=MATCH(1022,A5:A8) search for 1022 in the range A5:A8 and returns its matched index 3 (it does not found 1022 in the range A5:A8, and returns nearest matched value (1020) index as we have not specified match type and it treat it as 1 by default).

Wild cards in the MATCH function

Yes, we can use wild cards in the MATCH function, see the following examples.
MATCH - Example 2

Reference:

Please refer the below article for more Lookup & Reference Excel functions.
Lookup & Reference Excel Formulas

Please refer the below article for more Excel Functions.
Excel Formulas | Home


 
Related Resource External VBA Reference
Excel VBA Reference Project Management Reference
VBA Reference:
Excel Reference:
By |February 18th, 2013|Excel Formulas|1 Comment

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

One Comment

  1. September 11, 2014 at 9:11 PM - Reply

    kindly please send

Leave A Comment

This site uses Akismet to reduce spam. .


Related pages


project dashboard excelexcel macros not workingexcel button in cellfso.createfolderinteger vbahow to protect sheet in excelprompt vbastep by step pivot table excel 2010beginners sql tutorialhow to adjust column width in excelhow to change the row height in excel 2010macro vba tutorialedit hyperlink in excelwhat does vba mean in excelsas beginner tutorialtrim function in excelvba array declarationhiding cells in excelproject portfolio dashboard excelgetfile vbamerge and center excel 2007vba merge cellscreate sheet vbahow to make pivot table in excel 2007vlookup formula examplevba code to open excel filevba adodb referencevlookups tutorialpivot chart excel 2013vba copy rowexcel macro activate workbookvba instr syntaxswitch statement in vbaproject schedule template excelvba macro codingvba show userformdefinition of workbook in excelexcel macro close excelhow to change width of a column in excelremove blank columns in excelinsert a column in excellearn excel vbaprivate sub vbabusiness analyst tutorial pdfbasic vba in excelexcel vba sample projectstos codesexcel 2003 vbaexcel vba connect to access databasemerge rows excelms access import excel vbamultiple criteria excelif formulas in excel 2007how to find duplicate numbers in excel 2007excel if not blanklooping excelmacro excel programmingformula vbasyntax for sql commandsshortcut key to open excelbuilding a dashboard in excelhow to delete duplicates from excelexcel vba questionshow to hyperlink excelhow to learn vlookup in excel 2007excel if isblank thenunhide column a in excel 2010the macros in this project are disabled excel 2007vba cells functionexcel macro last rowxml to excel conversionms excel sorting