Forget Vlookup and Bring Index/Match into your life
A quick tutorial on why Excel’s most “familiar” tool is not the most optimal
What am I trying to do?
Use Worker ID to extract supervisor name from another tab/file:
We can see in the BETA file that Worker 11111’s Supervisor is Sally. Now let’s see how we can obtain that using two techniques.
How do I solve using Vlookup?
In the highlighted cell, input:
=VLOOKUP(A2, BETA!A1:D5, 4, 0)
Why do we do it this way?
A2 — This is where the Worker ID is in your file
BETA!A1:D5 — These are the rows and columns where the data we are extracting will be found
4 — Within these rows and columns, we count to the 4th column and see that it contains Supervisor name
0 — Traditional Excel coding
How do I solve using Index/Match?
In the highlighted cell, input:
=INDEX(BETA!D:D, MATCH(A2, BETA!A:A, 0))
Why do we do it this way?
BETA!D:D — This is the column that contains Supervisor name (i.e., the data you are in search of)
A2 — This is where the Worker ID is in your file
BETA!A:A — This is the column in the BETA file that contains Worker ID. Excel needs to use this column to match A2 to locate this person’s supervisor, so first it will find the row that contains Worker ID 11111 to do that. Read that again — this is a critical step.
0 — Traditional Excel coding
So why is Index/Match superior?
While both ways give us “Sally”, the true power of an Index Match comes down to the file from which we are extracting the data (i.e., BETA).
Three key things make Index/Match the superior choice:
Reduced Manual Effort: Note that in an Index/Match, we didn’t have to manually count to the column that contains Supervisor name. Index/Match doesn’t care what number the column is! It only wants to know the column reference of your data of interest. While in this example counting 4 columns is not too bad, imagine how tedious this can be for larger datasets!
Increased Efficiency: Note we used only 2 columns to extract the data (one with the Worker ID and one with the Supervisor Name). In a vlookup we have to highlight the ENTIRE dataset (and what’s the use the other columns don’t even contribute to the result!).
More Flexible: Columns can be anywhere in your lookup file. In a vlookup, the unique ID (in this case, Worker ID) must ALWAYS be the first column, or the vlookup just doesn’t work! In our example even though Worker ID was in the first column, it doesn’t have to be, as Index/Match is agnostic and we can place the Worker ID column anywhere.
Takeaway:
While vlookups are the more “familiar” option for most Excel users, using an Index/Match significantly removes the ambiguity and complexity inherent to a vlookup.
It simplifies the task by merely asking Excel, “Hey, I need a data point based on a unique identifier. I will provide you the location of the data point I need, as well as that of the unique identifier… can you Match based on the unique identifier I give you?”
Like with most tools in our Excel arsenal, practice makes perfect. While Index/Match may be the less “familiar” tool, keep using it and it will start to become incredibly intuitive until you begin to wonder how you managed to survive without Index/Match for so long…seriously, I promise!
Now, what are you waiting for? Go try it out!!
