Searching values in Excel (INDEX/MATCH)

Many basic Excel users for sure know VLOOKUP function and use them effectively to lookup data in different sets. Reminding syntax:
=VLOOKUP([searched value];[search array]; [column number];[exact value/ approximate value])

How INDEX/MATCH works?

Index formula set on column returns answer based on given row number.
=INDEX([Column];[Row number])
MATCH function returns row number based on looked up condition in given array.
=MATCH([Searched value]; [Search array]; [exact value/ less than/ greater than])
When we connect functions INDEX and MATCH we will get to the result where MATCH based on given criteria returns row number for INDEX function which will return searched value. It is shown best in below example:
=INDEX([Value I want to get]; MATCH([Searched Value]; [From where I want to return value]; [Type of returned value]))

What are advantages of INDEX/MATCH towards VLOOKUP?

Well, first of all we do not need to select whole table but only columns that take active part in equation. It works well when working on tables where we are using column names. Formula in this shape is much more transparent than VLOOKUP because we are using column names instead of selecting full table and counting number of columns to iterate through. Another advantage is direction of search, meaning we do not need to reorganize our table. In case of VLOOKUP searched value must always be first from the left in selected array. Personally I use more often INDEX/MATCH combination than VLOOKUP but it is a matter of habit.

Interesting article? Share further!

Leave A Comment