Match Formula in MS Excel

Match Formula in MS Excel

The MATCH formula in MS Excel returns the relative position of an item in an array that matches a specified value. It can be used to look up values in a table based on a specific criterion.

The syntax of the MATCH formula is as follows:

MATCH(lookup_value, lookup_array, [match_type])

Where:

• lookup_value: The value you want to match in the lookup array.
• lookup_array: The range of cells that you want to look up the value in.
• [match_type]: Optional. A number that specifies the type of match you want to find. There are three possible values:
• 1 (or omitted): Finds the largest value that is less than or equal to the lookup value.
• 0: Finds an exact match.
• -1: Finds the smallest value that is greater than or equal to the lookup value.

For example, if you have a table of data in the range A1:B10, with column A containing names and column B containing values, you can use the MATCH formula to find the row number for a specific name:

=MATCH("John",A1:A10,0)

This would return the row number in the range A1:A10 that contains the value "John".

Note: The MATCH formula is often used in combination with the INDEX formula to retrieve the corresponding value in the table.