Why you should know Index and Match function?

When you start using Excel, you know the number of functions(more than 400+) present in it. Even today we may not have applied even 50 functions to get out jobs done. Of course, the widely used one is Vlookup. The use of Vlookup is limited to looking up values from left to right. However, if you want to do a lookup from right to left Vlookup renders difficult. Index and Match functions come to save us during these situations.

If you divided Vlookup into 2, it would be Index and Match.

INDEX( table, row_number, column_number )

When I started using Index, I often found myself using it with Match to do a reverse Vlookup. However, it became beneficial when I started creating Dashboards. Remember this – Index takes a position and provides a value placed in that position. Following video explains basic use of Index.

MATCH (lookup_value, lookup_array, [match_type])

The Match is quite the opposite. It takes a value and provides the position. This position can then be used in the Index function to return a value. The only difference is that it used a 3rd argument which is similar to the 4th argument of the Vlookup. Following video explains basic use of Match.

To you test yourself, whether you have understood these functions try using them together to achieve what a Vlookup can do.

Kunaal

Kunaal is an Analytics Practitioner, Lifeaholic Evangelist, YouTuber, Learner Writer, and Apprentice Philosopher. He blogs on funxexcel.com about how to use Excel in a fun way and also how to get into Analytics.
Close Menu
%d bloggers like this: