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.

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( value, array,

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 – 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.

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