How to use Excel MATCH function

Introduction

MATCH function in excel is a lookup function. It helps in locating cell number corresponding to a lookup value of a row, column or table. It does this by searching for specific value in a range of cells and returns position of value. For Example, if the range A3:A6 have the values 10, 15, 20 respectively then function =MATCH(20,A3:A6,0) gives value 3 as 20 is 3rd in the range. It is also used commonly with the INDEX function.

Syntax

=MATCH(Lookup_Value, Lookup_array, (Match_Type))

Arguments

  • Lookup_Value:- Here you will put the value which you want to match in Lookup_array. For example, when you are searching a name in your contact list, it is the lookup_value, but actually you want to find the mobile number.
  • Lookup_array:- It is the range of cells or array reference in which lookup_value is being searched.
  • Match_Type:- It is used to specify how you want to match lookup_value with the lookup_array values. By default its value is 1. But it can have 0,1,-1 values.

MATCH_TYPE Argument

1 or omitted Find value less than (largest) or equal to Lookup_value.
0 Find value exactly equal to Lookup_value.
-1 Find value greater than (smallest) or equal to Lookup_value

Keynotes

  • MATCH is not case sensitive Or it do not differentiate between upper and lower case letters.
  • If no value is found it returns #N/A error.
  • A descending order is used for the lookup_array argument like 4,3,2,1,0.
  • If lookup_value is in text form and match_type used is 0, than wildcards (*,?) can be used in lookup value.
  •  It is commonly used with INDEX function to find a value instead of position as a alternate to vlookup.

Examples

This is an example of Match_Type value ‘0’, Here the MATCH function searches for exactly same value as written in Lookup_value.

This is an example of Match_Type value ‘1’, here the function searches for the largest value which is smaller than or equal to the lookup_value.

Leave a Comment