INDEX, OFFSET and MATCH are powerful functions in Excel that can be combined together to perform advanced data lookup operations.
Here’s what each function does:
- INDEX: Returns the value of a cell in a specified row and column of an array or table.
- OFFSET: Returns a reference to a range of cells that is a specified number of rows and columns from a starting reference point.
- MATCH: Searches for a specified value in a specified range of cells and returns the relative position of the value within that range.
By combining these functions together, you can create a formula that looks up a value in a table and returns a corresponding value from a different column or row.
This is particularly useful when working with tables that have changing ranges or when you need to do multi-table data lookups.
Using INDEX OFFSET MATCH in Excel to Lookup Data in Tables with Changing Ranges
One common use case for the INDEX OFFSET MATCH combination is to lookup data in tables with changing ranges, such as when new data is added or removed from a table on a regular basis.
Here’s an example code that uses INDEX, OFFSET, and MATCH to lookup a value in a table with a changing range:
Copy CodeSub lookup_data()
Dim table_range As Range
Dim lookup_value As Range
Dim result_value As Range
Set table_range = Range("A1").CurrentRegion
Set lookup_value = Range("B1")
Set result_value = Range("C1")
result_value.Value = Application.Index(table_range, _
Application.Match(lookup_value, Application.Offset(table_range, 0, 1), 0), 3)
End Sub
In this example, the Index() function is used to return the value in the third column of the table that corresponds to the row where a lookup value is found.
The Match() function is used to find the row where the lookup value is found, and the Offset() function is used to specify the second column as the search range.
Nesting INDEX OFFSET MATCH Formulas in Excel for Multi-Table Data Lookup
You can also nest INDEX OFFSET MATCH formulas in Excel to do multi-table data lookups.
Here’s an example code that looks up a value in one table and returns a value from another table based on the first lookup:
Copy CodeSub nested_lookup()
Dim table1_range As Range
Dim table2_range As Range
Dim lookup_value As Range
Dim result_value As Range
Set table1_range = Range("A1").CurrentRegion
Set table2_range = Range("D1").CurrentRegion
Set lookup_value = Range("B1")
Set result_value = Range("C1")
result_value.Value = Application.Index(table2_range, _
Application.Match(Application.Index(table1_range, _
Application.Match(lookup_value, Application.Offset(table1_range, 0, 1), 0), 2), _
Application.Offset(table2_range, 0, 1), 0), 2)
End Sub
In this example, two tables are involved: Table1 and Table2.
The lookup value is searched for in Table1, and the corresponding value from the second column of Table1 is used to lookup the corresponding value from the second column of Table2. The resulting value is written to cell C1.
