If you want to follow a proven system to always write perfect VLOOKUP/INDEX & MATCH formulas, then this free crash course is for you!
In this crash Excel course, you will learn:
- How to understand and remember the VLOOKUP/INDEX & MATCH syntax
- How to set up your data table for perfect matches
- How to get rid of #N/A errors once for all using the TIF blueprint
- And much more!
I was introduced to Excel by accident. I started a job and got a PC with Excel on it. The first tasks involved data entry and lists maintenance.
As my assignments evolved, I needed to do some basic queries…
That was the time when the VLOOKUP function made me feel the power of Excel.
Here are the pros why you should implement a VLOOKUP function
- It searches a value in the backend left index column and retrieves the specified attribute.
- It is easy to learn and write.
- It does not require any complicated DB relational stuff.
- It updates automatically.
- It can use wild cards.
After the initial feel, I found some shortcomings worth talking about…
Here are the cons why you should keep away of the VLOOKUP function
- Only searches a value based on a single condition.
- Only searches a value in the left index column (you keep moving columns in the backend list to implement the formula successfully).
- You can not know the location of the returned value (in terms of row or cell reference).
- It only retrieves the first instance.
- It is affected by a non sorted ascending index column.
- It does not allow you to search a value based on multiple criteria.
I still use the VLOOKUP formula because it is very easy to write; however, I cannot cope with the limitation of not being able to search multiple criteria, and the limitation to search only the leftmost column. I feel bored by moving columns to the left and trying to find out the solution to a multiple criteria lookup.
I now use the INDEX and MATCH formula. It appears complex at first, but once you get it, you can overcome those well-known VLOOKUP limitations and even get more versatility.
What do you think of the VLOOKUP gem?
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.
Today; I will share with you my love by the Excel Lookup Formulas…
Why do I love Formulas?
The relevant benefits of using formulas make them irreplaceable!
Reporting in Excel by writing lookup formulas is harder than dragging-and-dropping fields in a Pivot table Report but in my humble opinion, there are benefits you only get using formulas.
You may have heard your peers that are fans of Pivot Tables beating Formulas, and believe me; from time to time I beat them too. Especially because of two main reasons:
- When I feel reports are easier said than done
- When I cannot create as many reports as I would like due to the time prohibitive nature of writing all the needed formulas
Given said that, I think that the lookup formulas (VLOOKUP, INDEX and MATCH, SUMPRODUCT, Array Formulas, SUMIF,COUNTIF, SUMIFS, etc) are really the gems of Excel.
Here are the 4 reasons why I love reporting in Excel using lookup formulas…
1) I love Reporting in Excel using lookup formulas because I can arrange the data in any printable format
Reason: you can prepare your report layout first and then pull the answers you need in the given cells. Sometimes your boss needs specific figures arranged in a specific way. To create this advanced reports, formulas can extract values and put them in any given cell.
2) I love Reporting in Excel using lookup formulas because I can see instant changes made at the backend list
Reason: even if I made one change or mass changes at the backend table, I see the updated output immediately in the frontend report. This is very useful when I need to have control cells in analysis.
3) I love Reporting in Excel using lookup formulas because I can create complex computations
Reason: I can embed any given pulled answer from a table into a formula and create any given math calculation with this value.
4) I love Reporting in Excel using lookup formulas because I can check the source of the output in a cell
Reason: I can press F2 and see the ranges and criteria involved. Even better, you can change the criteria or ranges involved.
If you are not experiencing all these thrilling benefits yet…
The best part of formulas is that they occupy the space you assign for them, you can create advanced Excel reports in the format your end-users need. Additionally, you feel the freedom of finding patterns based on the queries you code in cells.
Definitely, the most loved feature is the immediate update you get when changes are made in any frontend control cell.