The Index Match Function in Excel: Powerful & Flexible
Learn more:- https://www.exceltutorial.net/
============================================================
Lookup Playlist:- • Lookup Formulas In Excel
============================================================
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, table_array, row_index_num,[col_index_num], [range-lookup])
lookup_value: The value you want to look for in the table.
table_array: The range of cells that contain the data you're looking for. This is where XLOOKUP starts looking for your lookup_value.
row_index_num: The row number within the table_array that contains the lookup value you want to return.
col_index_num: The column number within the row where you want to return a value.
range-lookup: If your search finds more than one match, this tells excel whether or not to find the closest match. TRUE means find an exact match; FALSE means look for the first matched value (this is how VLOOKUP works).
INDEX MATCH and XLOOKUP are both incredibly powerful functions that can do a lot more than just what we've shown here. With a little practice, you'll be able to use them in all sorts of ways to solve complex Excel problems. Stay tuned for more videos on INDEX MATCH and XLOOKUP!
Table Of Content
00:00 Introduction
01:35 Vlookup Function
06:58 Index Match Formula
10:40 XLookup Function
14:00 Conclusion
Other Playlists:-
Cell References in Excel - • Cell References In Excel
Logical Functions in Excel :- • Logical Functions
Count and Sum Functions in Excel - • Count And Sum Function
Learn Data Validation In Hindi: • Learn Data Validation In Hindi
Learn Excel Tutorials Online:- • Learn Excel Tutorials In Hindi
Excel Analysis Tutorial: • Excel Analysis Tutorials
Смотрите видео The Index Match Function in Excel: Powerful & Flexible онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь Excel Tutorial 13 Декабрь 2021, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 179 раз и оно понравилось 3 людям.