There are 3 separate match modes (set by thematch_typeargument), as described below.
Note: the MATCH function will always return the first match.
If you should probably return the last match (reverse search) see theXMATCH function.
If you want to return all matches, see theFILTER function.
MATCH only supports one-dimensionalarraysorranges, either vertical or horizontal.
you might evenuse MATCH twice in a single formulato find a matching row and column at the same time.
In other words, MATCH figures out theposition, and INDEX returns thevalue at that position.
For a detailed overview with simple examples, seeHow to use INDEX and MATCH.
Match jot down information
Match jot down is optional.
If not provided,match_typedefaults to 1 (exact or next smallest).
Whenmatch_typeis 1 or -1, it is sometimes referred to as an “approximate match”.
The default setting of 1 can cause MATCH to return results that look normal but are in fact incorrect.
Explicitly providing a value for match_type, is a good reminder of what behavior is expected.
Exact match
Whenmatch_typeis zero (0), MATCH performs an exact match only.
The resulting formula is called “INDEX and MATCH”.
The formula in F5 is:
In this example, MATCH is set up to perform an exact match.
The INDEX function then returns the 7th value from the range C5:C12 as a final result.
The formula is solved like this:
See below for more examples of the MATCH function.
Case-sensitive match
The MATCH function is not case-sensitive.
This formula is explained with anINDEX and MATCH example here.
you’re able to use INDEX to retrieve individual values, or entire rows and columns.
LOOKUP’s default behavior makes it useful for solving certain problems in Excel.
HLOOKUP Function
The Excel HLOOKUP function finds and retrieve a value from data in a horizontal table.
It is a more robust and flexible successor to the MATCH function.
XMATCH supports approximate and exact matching, reverse search, and wildcards (* ?)
XLOOKUP supports approximate and exact matching, wildcards (* ?)
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
This makes FILTER a flexible way to isolate and inspect data without altering the…
