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


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 only searches vertically


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


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



  1. Excel

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 10 years. He also provides Accounts Tax GST Training in Delhi and Pune.