Steps to create this combo formula: First of all, in a cell enter month name and in another cell enter the employees name.

Well, as we all know..VLOOKUP is one of the most popular Excel functions.

Right It can help you to quickly look up for a value in a column.

But..when you use it more and more you will realize that there are some problems with it.

In VLOOKUP, colindexno is a static value which is the reason VLOOKUP doesnt work as a dynamic function.

And this is where you need to combine VLOOKUP with the MATCH.

If you are working on multiple column data, its a pain to change its reference..you have to do (change column number) this manually.

Using MATCH Function with VLOOKUP To make you understand this formula I have to break it a simple part: Problems With VLOOKUP Why Match Function VLOOKUP and MATCH Together Problems Solved.so without any further ado, lets get started.

Problems With VLOOKUP I have found two biggest reasons to create a combination of these two functions.

Static Reference Just look at the below data table..where you have 12-month sales for four different employees.

Now, lets say you want to look up for the Feb months sale of John.

VLOOKUP (May,A1:E13,2,0) And in this formula..you have mentioned 2 as the colindexnum because Johns sale is in the second column.

But what will you do if your boss tells you to get the sales value for the Peter You need to change the value in colindexnum, because its not dynamic.

Add or Delete Columns Now think in a different way.

You need to add a new column for a new employee just before Johns column.

And here, Johns column number is 3 and your formula result is incorrect.

Again here, because colindexnum is a static value you need to change it manually from 2 to 3 and again if you need something else.

At this point, you are clear about one thing that you need to make colindexnum dynamic.

And for this, the best way is to replace it with the MATCH function.

Why Match Function Before you combine VLOOKUP and MATCH, you need to understand match function and its working.

The basic use of MATCH is to find the cell number of the lookup value from a range.

Syntax: MATCH(lookupvalue,lookuparray,matchtype) It has mainly three arguments, lookup value, a range to lookup for the value, and the match type to specify exact match or an approximate match.

For example, in the below data I am lookup for the name John with the match function from a heading row.

And, it has returned 2 in the result because the name is in the 2nd cell of the row.

VLOOKUP and MATCH Together Now, its time to put VLOOKUP and MATCH together.

First of all, lets create a formula by using both of the functions and then well understand how these two work together.

Index Match Excel For Mac