INDEXMATCH

INDEXMATCH is a commonly used approach in spreadsheet programs that combines the INDEX and MATCH functions to perform lookups. INDEX returns a value from a specified position within a range, while MATCH finds the position of a value in a range. Used together, they retrieve data from a table based on row and column coordinates.

Typical syntax for a single-dimensional lookup is: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). The 0 for the third argument of MATCH requests an exact match. For two-dimensional lookups: =INDEX(return_range, MATCH(row_value, row_range, 0), MATCH(column_value, column_range, 0)).

INDEXMATCH is more flexible than VLOOKUP: it can look up values regardless of column position and can return values from any column in the return range. It also allows lookup in a row instead of only in a column and does not require the data to be sorted.

Common practices include using IFERROR to handle missing values, e.g., IFERROR(INDEX(...), "Not found"). Using named ranges can improve readability and reduce errors.

Limitations include complexity for newcomers and potential errors if ranges are misaligned. In modern Excel, alternatives such as XLOOKUP or FILTER provide simpler syntax for similar tasks, while VLOOKUP remains available for compatibility.