Sharpen Your Excel Skills with Lookups, Index, and Match
Published: June 29, 2016
Author: Oliver Eldredge
Ah, vlookup…one of the fundamentals of any digital marketer’s Excel toolbox. As we all know, it’s great when it works, but it has pretty rigid limits. (If you’re new to this formula, then you might consider reviewing our beginner’s guide to vlookups here.)
In this post, we’ll take a look at a few common modifications to the formula that can help out in search, as well as alternative formulas that can accomplish some things that lookups cannot.
Consecutive vlookup technique
One of the most common and unnecessary time-wasters with this formula is when you want to pull several contiguous columns of data from one source to another. Under this circumstance, you would often end up editing the third parameter of your formula to contain the desired reference.
We’re interested in writing a single formula that can be used to fill the whole range from E2:F8:
The simple technique for accomplishing this is to write individual formulas for each column. With a little attention to absolute values, it’s fairly easy to write a single formula that works across multiple columns. To be able to drag the first parameter, we’ll want an absolute column reference; we’ll definitely want the entire second parameter to be an absolute reference (or named range); and our third parameter will pull adjacent columns if we use the column formula with an absolute reference for the first column reference but not the second.
=VLOOKUP($A2,$1$10:$C$17,COLUMNS($E:F),False)
Index/Match
Nesting the Index and Match functions can provide an alternative to Vlookup that offers a couple of advantages without any real drawbacks. Two advantages that are easily gained with this approach are that you aren’t restrained by having your lookup value on the far left of your table, and it generally works faster. It’s a worthwhile skill to learn, as both formulas have several applications that can be creatively used to solve a number of other challenges.
The Index function takes a simple array and returns whatever value is in a certain spot on the array. So in our example, if you set up =INDEX(A2:A8,5), the formula would return “Fri,” as that value is in the fifth spot in the array.
The Match formula tells you which spot in an array a certain value was first found. So in our example, if we set up =MATCH(“Fri”,A2:A8,0), the formula would return 5, as “Fri” was in the fifth spot in the array.
We can combine both of these formulas by setting up our match formula to determine what row we’re interested in retrieving data from, and using Index to pull data from the desired column.
Vlookup would not work in the example below because of the location of the lookup value in the array:
Take these new techniques out for a spin and enjoy the increased functionality. Good luck!
