The Match function is used to determine the relative row number or column number (or both). From there, we take those values and target a specific cell in the range that was passed to the Index function. For our formula, Match is returning the row number that matches the “Name” column.

You can even use MATCH twice in a single formula to find a matching row and column at the same time. Frequently, the MATCH function is combined with the INDEX function to retrieve a value at a certain (matched) position. In other words, MATCH figures out the position, and INDEX returns the value at that position.

XMATCH was developed to address these gaps by incorporating enhanced features such as defaulting to an exact match, reverse searches, and enabling binary searches—offering a more dynamic experience for heavy Excel users. The XMATCH function in Excel boasts a simple yet powerful syntax, allowing for diverse searching options.