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

BROWSE ALL TEMPLATES

50+ Project Management Templates Pack
Excel PowerPoint Word

VIEW DETAILS

Advanced Project Plan & Portfolio Template
Excel Template

VIEW DETAILS

Ultimate Project Management Template
Excel Template

VIEW DETAILS

20+ Excel Project Management Pack
Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack
PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack
Word Templates

VIEW DETAILS


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

LIMITED TIME OFFER - Get it Now!
Advanced Project Plan Excel Template

 
Related Resource External VBA Reference