Advanced Excel MATCH vs INDEX MATCH formula with example

Advanced Excel MATCH vs INDEX MATCH formula with example

MATCH

The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range.

Syntax

=MATCH(lookup_value, lookup_array, [match_type])

  • lookup_value: mandatory parameter. The value that you want to match in lookup_array. The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.
  • lookup_array: mandatory parameter. The range of cells in which search needs to be performed.
  • match_type: its an optional parameter. The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.

Exact MATCH example

Let’s search 5.6 in column “B” which can be located at row number 5 as shown below:

Output

Relative MATCH example

In above example we are searching for value 6 in Column B in relative search which finds closes match at row number 9 as shown below:

Example

INDEX with MATCH example

we will continue with above example and add INDEX function, since we know index requires following parameters:

  • Array
  • row_num
  • column_num

The row_num condition will be full filled by MATCH function. If you need more details in INDEX function please refer my previous article “Excel =INDEX formula with example“. Below example we will try to retrieve Car Name from Column “A” based on match:

Example

Output

Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.

Leave a Reply

Your email address will not be published.