In the previous segment, you learnt about the VLOOKUP function in detail. Now, you will learn about two new Excel functions called INDEX and MATCH.
When used together, INDEX and MATCH perform the same task as VLOOKUP, but with additional flexibility:
- VLOOKUP searches for the lookup value only in the first column of the table array.
- INDEX and MATCH allow the lookup value to be in any column of the table array.
In the following video, you will see a demonstration of how INDEX and MATCH work in Excel to retrieve data based on flexible lookup criteria.
As seen in the video, the INDEX and MATCH functions are used to get the source column from the AdditionalData table.
Syntax
- MATCH: MATCH(lookup_value, lookup_array, [match_type])
- INDEX: INDEX(array, row_num, [column_num])
The INDEX function returns the matching value, while the MATCH function returns the index of that value.
Unlike VLOOKUP, which always looks for values to the right, INDEX-MATCH allows:
- Flexible lookup in any column
- 2D searches across rows and columns
- Dynamic indexing that doesn’t break when columns are inserted
Using VLOOKUP together with MATCH allows dynamic indexing. This ensures that even if you insert a column into the data array, the formula continues to work correctly.
For more details on 2D matches and dynamic indexing, you can refer to this link.
Now that you have a basic understanding of INDEX and MATCH, attempt the questions shared below using Superstores_data.
In the next segment, you will learn about the Pivot Table feature in Excel.