Index Formula vs. Vlookup Formula





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

INDEX:The Index formula 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:“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:The 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” function 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 “VLOOKUP” with “MATCH” function

  • 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 “INDEX” with “MATCH” function

  • 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.



Leave a Reply

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


8 − six =

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>