facebookIcon tracking


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.
Pasted image at 2016_06_29 09_29 AM


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:
Pasted image at 2016_06_29 09_31 AM
Take these new techniques out for a spin and enjoy the increased functionality. Good luck!

1 S Wacker Drive
Suite 2250

Chicago, IL 60606(650) 539-4124


Want to become a client?

Contact Us decorative arrow

Want to join the team?

View Our Openings decorative arrow

Find us on social media.

Press inquiries.

Email Us decorative arrow

Expert insights for your inbox. Subscribe to our content.

Accept No Limits.