Learn more:- https://www.exceltutorial.net/

Lookup Playlist:- https://youtube.com/playlist?list=PLhvn6aQRdimXjKB1EOoRWznYZtqRe7B2z

In this video, we are going to learn about INDEX MATCH function in Excel. INDEX and MATCH combination is a very powerful tool if we compare it with VLOOKUP. As you know VLOOKUP looks right but has its limitations which are described in the video itself. So we have categorized the video into three parts i.e.,

The first part will be all about VLOOKUP Formula Explanation and its limitation

First Letsee the VLOOKUP Syntax Below;

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

As we know VLOOKUP function looks to the right and searches vertically. But it has its limitation when we apply it horizontally as in the video. Therefore to resolve that we have used a helper function like the MATCH function to match the header with the table.

The second Part Will Be using INDEX and MATCH functions as alternative to VLOOKUP.

The INDEX and MATCH function works in a similar way to VLOOKUP but with more power. The INDEX function takes two arguments: the first is the row number you want to be returned from the lookup table and the second is the column number you want to be returned from that row.

To understand INDEX MATCH, we first need to know about the INDEX function as it serves as a building block for the INDEX MATCH Function. INDEX formula takes two arguments: Row number and Column number. INDEX function always starts from the first row and the first column in a table. So, if you want to return the value in cell C12, INDEX would start at Row=l (because there are 11 rows below the current row) and Column=12 (because there are 12 columns to the right of the current column).

The INDEX function returns the value in a cell at a specific row and column. In order to return the correct value from an INDEX formula, you need to provide it with both of these parameters: The Row number and Column number. INDEX MATCH works similarly but allows for multiple criteria which can be used as conditions or as a way to create more complex INDEX MATCH formulas.

So =INDEX(D:D,MATCH($A$12,B:C,0)) will return the value in B11 because MATCH returns an exact-match score for each row/column intersection (a number between 0 and -32,768). INDEX will start at the first row and column in the table, which is Row=l and Column=A.

If we want to return a value from a different row or column, INDEX MATCH can do that too!

Third, we have used XLOOKUP to replace both functions above. If you have a subscription of office 365 then it's the most powerful formula. Lets understand the syntax of XLOOKUP;

=XLOOKUP(lookup_value, ... https://www.youtube.com/watch?v=AVR2Mx6HwbY