Link to Buy Book for beginners:- amzn.to/3hDkznR
Kindle Edition:- amzn.to/3hABDe5
Watch this video tutorial to find out how you can use Index & Match for more complex lookup problems which can't be solved by Vlookup. when we use index match together its became more powerful than Vlookup. This video shows you how Index Match works with simple and to the point best explanation. It first shows you how Index works on it's own and then how Match works and then puts the two Excel functions together. watch this video to know about index match excel formulas in Hindi.
What is INDEX MATCH in Excel?
The INDEX MATCH Formula is the combination of two functions in Excel: INDEX and MATCH.
=INDEX() returns the value of a cell in a table based on the column and row number.
=MATCH() returns the position of a cell in a row or column.
Combined, the two formulas can look up and return the value of a cell in a table based on vertical and horizontal criteria. For short, this is referred to as just the Index Match function. To see a video tutorial, check out our free Excel Crash Course.
#How to Use the INDEX Formula
Why Index Match is better than Vlookup & why use Index Match instead of Vlookup?
Here are 3 reasons why Excel experts generally substitute VLOOKUP with INDEX and MATCH.
1. Unlike VLOOKUP, which searches only to the right, INDEX and MATCH can look in both directions - left and right.
2. INDEX & MATCH can perform two-way lookups by both looking along the rows and along the columns to find the intersection within a matrix.
3. INDEX & MATCH is less prone to errors. Assume you have a VLOOKUP where the final value you want returned is in column N. Your lookup value is in column A. You need to highlight the entire A to N range and then provide your index number to be 14. If you happen to delete any of the in-between columns, you would have to update that index number. You don’t need to worry about this when you use INDEX & MATCH.
All in all, INDEX and MATCH is more flexible than VLOOKUP.
Here are some of the reasons why Excel experts generally substitute VLOOKUP with INDEX and MATCH.
Note: Index match is not case sensitive
Index explained:
The first argument of INDEX is to give it an array. This array (range) should include your answer. You then need to specify how many rows to go down and how many columns to move to find the correct value. You cannot move outside the INDEX range.
The syntax of INDEX is:
• The range where the return value resides.
• Number of rows to move down (the row index)
• Number of columns to move to the right (the column Index). This argument is optional. If you only have one column, you can leave the column argument empty, otherwise, you need to specify the number of columns to move over, in the range.
The MATCH function’s syntax is as follows:
• Like VLOOKUP, Match needs a lookup (target) value. The user can reference a cell or directly type the value into the formula.
• Lookup_array: The “list” (range) where the return values are located.
• Match_type: 0 for an “exact” match.
Index MATCH used together:
When INDEX and MATCH are used together, the MATCH function finds the look up value’s row / column index and then hands this value off to the INDEX function to get the lookup value.
0:00 Intro
1:15 Index
2:58 Match
3:55 Index Match