Hi,   I celebrate today another newsletter and that I will eat a delicious local dish: mashed grill plantain (a kind of banana) with a handy made pulpy mass of peanut. It is usually served with coffee!

When you come to Ecuador, drop me a line and I can prepare one of these for you with my family.

Today, we invited my brother and his wife to share this recipe, so we will have a wonderful time here.

I like eating cultural dishes. I imagine the origins of the recipe, maybe passed by mouth from ancient tribes and the like.

I hope to eat the most popular dishes from many different countries soon.
With anticipation, we have more time in the future to talk about your favorite meals. However this is about Excel so let’s move forward…   I will show you here why you don’t need to use VLOOKUP anymore!

Come with me…

The fact that Excel VLOOKUP function receives a lot of word of mouth means that you feel you’ve missed something if you don’t use it too.   And so, as you hear about it, you’ve asked your peers or researched on Google about how to put this function to work for you. Now you may think it was for sure one of the best moves you did in Excel.

But it is also true that…

If you have been using VLOOKUP for a while, you may have realized it’s well known shortcomings: not being able to look up other than the left index column of the table array, unable to look up in multiple columns with complicated criteria, to name a few.   So, you may have heard about the next move…Yes, the “growing in popularity” INDEX and MATCH formula.

If you have been using INDEX and MATCH formula, you may be wondering why you should use VLOOKUP, if you now can do its tasks and even more already.   I think you keep thinking of VLOOKUP because you entered the advanced world of Excel through this amazing Excel function. Before using VLOOKUP, you were for sure copying and pasting data from tables to create reports.   So this question is very relevant now: should I keep using VLOOKUP function if the INDEX and MATCH formula does the same things?

Let’s listen to John from Australia…   After he learned how to use the Index & Match formula he sent me an email…   “From reading your Lookup book I now use the Index/Match combo and rarely Vlookup. It seems to me it does all the same things with the 2 advantages that the list doesn’t need to be sorted and you can look left and right of the reference. Am I missing something in this?  If I am when do you use either?” John H from Brisbane Australia

I replied to John…

“Yes, it does the same things with the key advantage that you can look up in any column of the table array. The column needs to be sorted when you perform an approximate match (MATCH function) as you do in Vlookup.

You don’t need to use Vlookup ever again if you are already comfortable using I & M.” Vlookup remains as a recurrent option for some users because it is easier to write and read and because Vlookup appears to be the trend. I still use it for simple lookups.   But INDEX and MATCH overrides VLOOKUP!
Let’s see two main reasons why you should use INDEX and MATCH

• You can look up any column of your table array (from the leftmost to the rightmost) and retrieve any corresponding column/field value (from the leftmost to the rightmost)
• You can adapt the formula to build very advanced lookup formulas like: case sensitive lookup, multiple column vlookup, multiple sumif, multiple countif, etc.

Let’s illustrate the first point with an example…   Imagine you have the table below…

Product, Price, Top Month

A, 100, January

B, 200, March

C, 120, October

D, 250, December

And you want the answer for this question: what is the price of Product A?   The formula would be =VLOOKUP(“A”,$A$2:$C$5,2,0) that returns 100.

But if you ask…   What is the price of the Product whose top month was October?   Sorry VLOOKUP. A land for INDEX and MATCH formula…   The formula would be =INDEX($A$2:$C$5,MATCH(“October”,$C$2:$C$5,0),2) that returns 120   So nothing to lose. And even better by using I & M you get familiar with the advanced formulas you are in need to develop to have your work done.   Yes, you later create more advanced reports using this scalable formula.

That’s all for now.