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 HLOOKUP INDEX 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

 

 

Go Ad-free
CA Maninder Singh's photo - Co-founder, Teachoo

Made by

CA Maninder Singh

CA Maninder Singh is a Chartered Accountant for the past 14 years. He also provides Accounts Tax GST Training in Delhi, Kerala and online.