How to Use INDEX MATCH in Excel to Give Copy & Paste a Rest
SEO professionals, social media strategists, and site analysts alike can sympathize with the struggle of unordered data lists. Despite the promise and allure of “all-in-one” data aggregators, many multivariable data reports require the use of multiple tools and websites in conjunction.
But when exporting data, all sorting mechanisms are not created equally. Some may be sorted alphabetically, by relevance, or by some arbitrary value A or B. When you throw in the fact that many reports omit data rows with insignificant or invalid data, even Excel’s robust data sorting function can’t make differing data sets align. That’s where INDEX/MATCH comes in. INDEX/MATCH can dynamically search and order values from disparate sorting systems, saving you the hassle of manually copying and pasting cell data points one. At. A. Time.
It’s time to banish that particular productivity sucker. Let’s learn how to INDEX/MATCH.*
*Fans of the much more popular VLOOKUP Excel function will be quick to jump up and defend it as being easier to use, but as you’ll see, INDEX/MATCH has some unique perks that VLOOKUP doesn’t—perks that make its slightly lengthier equation much less intimidating.
When to Use an INDEX/MATCH Function
Step 1 requires us to first back up and ask: what does an unordered list look like, and why is it so annoying? Take the following example.
Here we have a list of tracked pie search keywords for a fictional, Oregon-based pie company: “Portland’s Tart Land.” Then, through Google AdWord’s Keyword Planner, we generated monthly search volume data for the same keywords.
The problem, as alluded to earlier, is that while AdWords provides a neat ordering of keywords from highest to lowest value, the ranking data doesn’t have an intuitive ordering. The non-alphabetic ordering of AdWords’ 3 keywords with little-to-no search volume only complicates things further.
Since we want to prominently feature rankings in our client report to Portland’s Tart Land, our gut reaction would be to manually copy and paste the search volume data from each cell in Column B into Column G. However, even in a reasonable list of 14 keywords, this could become a tedious time sink very quickly—just imagine doing it with 300 keywords.
Thankfully, using INDEX/MATCH in Column G will make sorting a breeze. And while VLOOKUP is unable to search columns to the left of it, INDEX/MATCH will be able to search Column B without trouble. Let’s look at the function itself.
How to Set Up an INDEX/MATCH Function
Whether this looks confusing to you or not, understanding INDEX/MATCH really is quite simple. The unformatted equation takes this form:
Let’s break down what each of these colored values means, and how we got the variables we did in the screenshot above.
indexed-columns – In this variable of the equation, select the entire span of columns (or rows; INDEX/MATCH can tackle both dimensions of data, unlike VLOOKUP, which is column-restricted). Since we wanted to search through Google AdWord’s Keyword column and get the data from its Search Volume column, our index consisted of columns A:B.
cell-to-look-for – This variable tells the function what value or term it will be looking for in the indexed area you chose. In column G2, we want to get the search volume data for “pie,” which is in cell E2.
column-to-search-in – This space indicates which column the “cell-to-look-for” will be found in. Since we’re looking for “pie” in the Google AdWords Keyword column, we chose A:A for our function.
0 – You may notice the zero within the INDEX/MATCH equation. This usually doesn’t need to be changed, since the 0 is an important indicator to Excel that you want an exact value match for your index search. Make sure not to remove it: without the 0, Excel will assume there is a 1 there, and having a 1 in this function will return the largest value less than or equal to your search rather than the exact value. TL;DR In most cases, leave the 0 alone.
indexed-column-with-results – Finally, this variable defines the column within the index where we want to find data for the “cell-to-look-for.” In our case, we want the search volume for “pie,” found in Column B. But putting B:B into our function will not work, because INDEX/MATCH wants to know the number of the column within the index. Our entire index consists of 2 columns, and the search volume column is the second within the index. That’s why we chose 2.
Our equation returned the correct value for “pie,” and by dragging the bottom right blue square in cell G2 downwards…
We can automatically get the data for the rest of our keywords. Unlike VLOOKUP, this INDEX/MATCH function is dynamic: if you insert new columns anywhere, the formula will auto-adjust itself instead of providing incorrect data.
Important note: While the formula does auto-adjust, if you ever plan to remove the indexed data (like the AdWords data we have in Columns A and B), you’ll want to make sure the columns with an INDEX/MATCH equation don’t provide a #REF! error when looking for non-existent data. To painlessly prevent this, after completing your INDEX/MATCH data search, copy the contents (all of Column G in our case) and paste their values in place. To do this, click Edit -> Paste Special.
INDEX/MATCH vs. VLOOKUP and Some Helpful Hints
That’s all there is to it! In addition to the basics here, INDEX/MATCH has a lot of built-in versatility. For example, the function can work between sheets (unlike VLOOKUP), allowing you to match data from otherwise independent data sets.
Additionally, if the term you’re searching for in an index can’t be found—sometimes data providers, like SEMrush, completely exclude data points with unquantifiable or non-available values—INDEX/MATCH will return an “#N/A” in that cell. If you’d like to customize that error text to something more meaningful, you can do so by adding an IFERROR wrapper to your INDEX/MATCH function.
You can see that at work here. We removed the keywords with low search volumes (as some programs might), and our IFERROR wrapper changed the jarring “#N/A” into a more friendly message. This helps make reports more intuitive for clients and laypeople.
Here’s the updated function:
=IFERROR(INDEX(indexed-columns,MATCH(cell-to-look-for,column-to- search-in,0),indexed-column-with-results),”Custom Error Message Goes Here!”)
INDEX/MATCH is a rewarding Excel function that’s a great step-up for those tired of manually sorting data or those sick of dealing with VLOOKUP’s shortcomings. To summarize, INDEX/MATCH’s advantages over VLOOKUP are: sorting data across sheets, reading columns to the left, compatibility with rows, and the ability to update when new columns are created.
For any SEO analyst, PPC campaign manager, social media strategist, or entrepreneur, INDEX/MATCH is a time-saving tool worth having in your spreadsheet arsenal.