Match function in Excel

Hello all. Let’s look at the MATCH function in excel. Match function will return the position of the lookup value as numerical value. Match function supports approximate and exact matches (in the form of optional argument).

Syntax

=Match(lookup_value, lookup_array, [match_type])

Working Examples:

In the below data set, we have the lookup value as the name (in the form of drop down) and the lookup array as A1:A11. We are looking for exact mach and hence give 0 as match_type.

The function returns an output of 6 and yes, “sourav” is at A6 – however, if you start from the first name, leaving the header, the position is 5. So you need to careful while using the formula – either we can ignore the header while selecting the lookup_array or decrement the match function by “1”. Like this

Match function also approximate matches. If you enter “1” as match_type, the function would return next smallest value and if “-1” is entered, then the function would return the next highest value. By default the function would take “1” – so please ensure you enter “0” if you are looking for exact match.

Match function also supports wild card match – we need to ensure we enter “*” at the end of the text string to get the wild card match.

In the above example, we have “Sa*” as the lookup value and the function returns “1” – the function returns the first best match, though we have another value (Sandeep) that would match “Sa*”.

Pointers
  1. Match function is not case sensitive
  2. Lookup value, within the match function, can have upto 255 characters
  3. If there are multiple match results, the function would return the first best match

Match function is frequently used with the Index function. I hope you like this section. Thanks for reading!

Leave a Reply

%d bloggers like this: