The INDEX and MATCH functions in Google Sheets are advanced lookup tools that are more flexible than VLOOKUP and HLOOKUP. They allow you to retrieve data from tables based on both row and column criteria and work even when your lookup column is not the first column.

1. INDEX Function

The INDEX function returns the value of a cell at a specific row and column within a range.

Syntax:
=INDEX(reference, row, [column])

• reference — The range of cells
• row — The row number within the range
• column — (Optional) The column number within the range

Example:
=INDEX(A2:C10, 3, 2)

• Returns the value in the 3rd row and 2nd column of the range A2:C10

Use Cases:
Retrieve specific values from a table
Dynamic referencing for dashboards
Combine with other functions for advanced calculations

2. MATCH Function

The MATCH function returns the relative position of a value within a range.

Syntax:
=MATCH(search_key, range, [match_type])

• search_key — The value you want to find
• range — The range of cells to search
• match_type — 0 for exact match, 1 for less than, -1 for greater than

Example:
=MATCH(101, A2:A10, 0)

• Returns the position of 101 in the range A2:A10

Use Cases:
Find the position of a specific value
Combine with INDEX for flexible lookups
Locate items dynamically in lists or tables

3. Combining INDEX and MATCH

Using INDEX and MATCH together allows you to perform lookups dynamically.

Example:
=INDEX(B2:B10, MATCH(101, A2:A10, 0))

• MATCH(101, A2:A10, 0) finds the row number where 101 appears in column A
• INDEX(B2:B10, …) returns the value in column B at that row

Benefits of this combination:
Lookup values in any column, not limited to the first
More flexible than VLOOKUP
Works with both vertical and horizontal tables

4. Tips for Using INDEX and MATCH

Use exact match (0) to ensure accurate results
Combine with IFERROR to handle missing values
Ideal for large datasets and dynamic dashboards
Can replace VLOOKUP for more complex lookup scenarios

5. Advantages Over VLOOKUP/HLOOKUP

No limitation on lookup column position
Works well with large datasets
Flexible with dynamic ranges
Easier to maintain in complex spreadsheets

Conclusion

INDEX and MATCH in Google Sheets provide a powerful, flexible alternative to traditional lookup functions.

Mastering this combination allows you to perform advanced data retrieval, build dynamic reports, and handle complex datasets efficiently.