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 do the same job as VLOOKUP; it also gives you additional flexibility in the indexing of the lookup value in the table array. VLOOKUP searches for the lookup value only in the first column of the table array; whereas, the INDEX and MATCH combination allows you to have the lookup value at any column index. In the following video, let’s see how they work with a demonstration.
As seen in the video, the INDEX and MATCH functions are used to get the source column from the AdditionalData.
The syntaxes are as follows:
MATCH(lookup_value, lookup_array, [match_type])
INDEX(array, row_num, [column_num])
The INDEX function returns the matching value, whereas the MATCH function returns the index. As VLOOKUP always looks for the values to its right side, INDEX-MATCH can be a better alternative here. The INDEX-MATCH function can perform the functions of both VLOOKUP and HLOOKUP. They can also perform 2D searches. You can refer to this link for more details on 2D matches.
You have learnt how to use VLOOKUP and MATCH functions. They are a powerful duo when it comes to dynamic indexing. If you try inserting a column into a data array, the pre-written VLOOKUP formula will throw an error because the third argument in the lookup formula has changed. In order to overcome that, VLOOKUP is used in conjunction with the MATCH function, which makes the indexing dynamic. You can refer to this link to see how it works.
Now that you have a basic understanding of INDEX and MATCH, attempt the question shared below.
Let’s move on to the next segment where you will learn about the pivot feature in Excel.