Index Match Formula VS Match Vlookup in Excel

Follow by Email
Facebook
Facebook
Google+
http://www.exceltip.com/lookup-formulas/index-formula-vs-vlookup-formula.html">
SHARE




In this article we will learn how we can use to Index Match formula at the place of Vlookup and Match formula in Microsoft Excel 2010

What are the advantages of using the “INDEX” formula instead of “VLOOKUP” formula?
How the “INDEX” formula calculates and returns results:-

Index function returns a value from the intersection between the row number and the column number in an Array. There are 2 syntaxes for the “INDEX” function.

1st Syntax of “INDEX” function: =INDEX (array, row_num, [column_num])

2nd Syntax of “INDEX” function:=INDEX (reference, row_num, [column_num], [area_num])

 

VLOOKUP  formula returns a value from the intersection between the found lookup value in the leftmost column and a column index number in the Table Array.

Syntax of “VLOOKUP” function: =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

 

Use the Match formula to find the row, column and index column number when using both Vlookup & Index formulas.

Match formula returns the cell number where the value is found in a horizontal or vertical range.

Syntax of “MATCH” function:=MATCH(lookup_value,lookup_array,[match_type])

 

Let’s take an example to understand “INDEX” formula VS “VLOOKUP” formula along with “MATCH” function.

Example 1:

We have the data set for 2 employees.. 1st data set in range A2:E13 contains the details like emp. code, date of joining, emp. name, designation, and contact number. And 2nd data set containsemp. code and date of joining required from the 1st data set.

img1

 

Use Match and Vlookup

  • Select the Cell H3, and write the formula.
  • =VLOOKUP(G3,$A$2:$B$13,MATCH($H$2,$A$2:$E$2,0),FALSE)
  • Press Enter.
  • The function will return the date of joining.
  • To return the date of joining for the rest of employees, copy the formula by pressing the key “CTRL+C” and paste in the range H4:H13 by pressing the key “CTRL+V” on your keyboard.

img2

 

Use Match Index formula:-

  • Select the Cell H3, and write the formula.
  • =INDEX($B$3:$E$13,MATCH(G3,$A$3:$A$13,0),1)
  • Press Enter.
  • The function will return the date of joining.
  • To return the date of joining for the rest of the employees, copy the formula by pressing the key “CTRL+C” and paste in the range H4:H13 by pressing the key “CTRL+V”.

img3

 

To remove the formula from the data use the “Paste Special” option:-

    • Select the range H3:H13, copy by pressing the key “CTRL + C”.
    • Right click on the mouse and select “Paste Special”.

img4

 

  • In the dialog box select values and click OK.
  • The formula will be replaced by values.

The main difference between the two formulas is that while using Index there is no need to organize the data table into a special format – you simply find the intersection between a row and a column. When using Vlookup, though, you always need to move the lookup column to the left side of the data table.

Please follow and like us:
6


Leave a Reply

Your email address will not be published. Required fields are marked *


− one = 1

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>