<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=539359806247306&ev=PageView&noscript=1"/>

Remember we used Vlookup and Hlookup function for

  • Bringing data from one sheet to another sheet
  • Comparing Data

 

Same thing can also be done by using combination of INDEX and MATCH function as shown below

 

 

Formula of INDEX  

=INDEX(Select Area ,Write Column No or Row No)

 

Formula of Match

=Match(What to look for,Where to look for,0)

 

Formula of Index and Match

 =INDEX(Select Area from where data required,Match(Select Item,Select Area containing Item,0))

 Note:-Press F4 while selecting both areas

It is a common formula in place of Vlookup and Hlookup

Note:-

Formula of Index and Formula of Match  are  not very useful to learn individually is

however,combination of both is very important function which we should know and practice

 

Assignment 1

Re-solve Vlookup Assignment using Index and Match

 

Assignment 2

Re-solve Hookup Assignment using Index and Match

 

 

Assignment 3

Compare two tables using Index and Match

Use of index and Match Function - Index and Match Function

Advantages of Using Index and Match over Vlookup Hlookup

 

VLOOKUP AND HLOOKUPINDEX AND MATCH

Vlookup only searches vertically

and

Hlookup only Horizantallly

It can be used for searching both

horizantally and vertically

 

Area should be on righr side of item in case

of Vlookup

and

Area should be on downwards item in case

of Hlookup

No such problem

In case,we insert column or row,

Vlookup or Hlookup may not work properly

No such problem

 

 

About the Author

CA Maninder Singh's photo - Expert in Practical Accounts, Taxation and Efiling
CA Maninder Singh
CA Maninder Singh is a Chartered Accountant for the past 6 years. He provides courses for Practical Accounts, Taxation and Efiling at teachoo.com.
Didn't understand? Ask your question or doubt free.

Apna sawal puchiye!

Jail