The MATCH function is a lookup and reference function which returns the position within an array of a specified value. If no matches are found then the function will return an error message. You can select if you want to limit your results to exact, or similar matches.

This is the format of the MATCH function:

=MATCH(LOOKUP VALUE, LOOKUP ARRAY, LOOKUP TYPE)

Here is an example of the MATCH function:

=MATCH(“Banana”, {“Apple”, “Banana”, “Pear”, “Orange”}, 0) = 2

MATCH function returning the index position of the searched value

This function is valuable when needing to extract the location of specified items within an array of data. The MATCH function is consistently paired with functions like INDEX or VLOOKUP to add additional dimensions of functionality to them.

An example of a functional implementation of this would be the creation of a report or template structure, which you would like users to be able to return only the information they request, as to provide a more concise and targeted report. The values which the users will be searching with are in the far-left column, meaning you can use a VLOOKUP, and the specific metrics users can request are titled along the columns of the first row of the table.

ex: The table has the following titles in cells A1:E1: Search key, Average Sales, Peak Sales, Sales Trend, Stock Turn Ratio. You want to return the Peak sales for the Search Key “FG001056”.

=VLOOKUP(“FG001056”,A:E,MATCH(“Peak Sales”, A1:E1, 0), FALSE)

* Note that this example pairs the MATCH function with the VLOOKUP function. For more information on VLOOKUP please visit its dedicated function page *