How to use Index and match function in Microsoft Excel

How to use Index and match function in Excel

How to use INDEX MATCH instead of VLOOKUP 

This lesson shows you how to write formulas using INDEX and MATCH to let you perform lookups that VLOOKUP can't, and which run much faster on large spreadsheets than VLOOKUP. This lesson explains how INDEX and MATCH work on their own, and then shows you how to write an INDEX MATCH formula that can look left as well as right, and performs much faster than VLOOKUP on large tables. 

INDEX MATCH works very well if your lookup data is not in the first column, or you want to look to the left of the lookup data, rather than to the right (which is all VLOOKUP can do).

Why INDEX MATCH is so much better than VLOOKUP?

For many users, VLOOKUP just does what they need. But there are three scenarios
 where VLOOKUP falls short. That's where INDEX MATCH comes in handy:

• VLOOKUP can only look from left to right.
  • You look a value in one column in a table, and then return a value from a column to the right.
  • But what happens if you want to look from right to left? VLOOKUP simply can't do that. INDEX/MATCH can.
• VLOOKUP is prone to error, especially in big spreadsheets

With VLOOKUP, you specify a number to represent which column you want to return the value from. So you have count the columns to figure out which one you want. Hopefully you'll notice if you get it wrong but what if you don't? Finding and debugging this error can be a nightmare. With INDEX MATCH, you select the specific column of data from which you want to return the value. That's much more reliable and easier to debug when things go wrong. 

For Example:

= Index(A:A,MATCH(A9,$B1:$B6,0))

Note: Using Vlookup you can lookup a value Left to Right..But using Index,match you can lookup any value in Any angle even no need to change Column Number Manually Simple drag and drop.









Do you like this page.? Please register your Email Id for News Letter..!! Or Like this page on Google+ and Facebook..!!