Hi,

Are you intimidated by the complex appearance of some Excel formulas?

This is an important point…

I was afraid of a large group of formulas like Array Formulas, INDEX and MATCH, SUMPRODUCT, OFFSET and many others.

But I am not scared anymore.

Well…

Sincerely, I am still afraid of many others like GETPIVOTDATA for example.

Don’t miss what I have to say you now…

Diane Berron, one of my valued subscribers replied me with this message…

“You make me laugh, there is nothing to fear, getpivotdata is nothing but a simple function that returns data found in a pivot table…”

She is so true. With fears, laugh loudly!

You know what I do with my fears?

I face them…

Trust me on this: I have learned that staying in your comfort zone is a suicide

We as humans have a natural predisposition against new things, and a predisposition towards the familiar.

You know that is right, don’t you…

So; no pain, no gain. Babies learn to walk by first falling down, then getting back up and falling down again, and getting back up again until they get it.

I spent a lot of time with INDEX and MATCH formula until I get it; the first step was to explore it. I take action when I hear people rumoring about a useful tool, I test those rumors always.

The benefits of leaving outside my comfort zone were enormous in this area: more productivity, more confidence in building reports my boss and end-users wanted, etc.

Here’s the story…

INDEX and MATCH formula is not so easy to read and write at the beginning. I kept away of it for almost two years. But I paid the price, I moved the needed column to the left of the backend table each time I used the VLOOKUP function.

The fact is that doing this backend-editing on a constant basis made me aware that this was a very inefficient process. Sometimes, I needed to search different fields on the same backend table. I could not imagine having different tables with different columns layout just for satisfying the VLOOKUP requirements.

Then, I discovered the functionality of the INDEX and MATCH formula; here are the 3 reasons why I love it…

1) I love the INDEX and MATCH formula because I can search the backend list as it is

Reason: I don’t need to move columns to the left of the backend list. Now; I can search any field by changing the formula instead of the lookup table.

2) I love the INDEX and MATCH formula because I get the position of the found value

Reason: the MATCH function returns the row number in which the lookup_value was found. I can later use this position to make extra computations or embed this value in other formulas.

3) I love the INDEX and MATCH formula because I can use it as part of an Array Formula

Reason: being familiar with the INDEX and MATCH formula facilitates your life when you need to write Lookup Array Formulas, since they use the INDEX and MATCH combo as the base formula.

Summary

The INDEX and MATCH formula appears daunting at first, but you replace the VLOOKUP function since this formula offers the same functionality and even more.