INDEX MATCH for PPC: A match made in heaven
For the Excel savvy amongst us, you might be familiar with Excel’s Index and Match respective functions. The former is used to find a cell’s value in a table using its column and row number, while the latter finds the position of a cell in a row or column.
The combination of these two functions is ingeniously named INDEX MATCH, and this new fusion-function allows you to turn the whole value range into a grid and select exactly the cell that you’re looking for. That is to say, you can look up values not only from right to left, but from left to right as well, and query values through lines as well as segments.
Spreadsheet fanatics hold onto your hats, because that’s only just the beginning.
From VLOOKUP to INDEX MATCH
If you’ve ever despaired that you’ve had to spend hours counting spreadsheet columns instead of optimising campaigns, or that you’ve been limited to the left side of the screen, chances are you’re a VLOOKUP survivor.
INDEX MATCH, by contrast, allows you to have dynamic column references, to add as many columns as you like (don’t go crazy now), and to locate your look up value in any column or row. Not only that, but it’s also compatible with both Google Sheets and Excel. See, we told you it got better.
How to use INDEX MATCH
Beginning with INDEX then, this part of the function returns the context of a cell which has been specified by a row or column index.
The syntax looks like this:
INDEX (reference, row, column)
The breakdown:
- Reference = the range you want to look up
- Row = row number your target cell is in
- Column = the column number your target cell is in
For example:
If you applied =INDEX(A1: C3, 2, 3) to an Excel spreadsheet (by typing it into an empty cell) you’d be presented with the value in C2 because it’s the second row and third column in the range.
You can even apply this formula for just rows or just columns. For example, +INDEX(A1:A7, 2) would provide results for the value in A2 because it’s the second row of the column.
Whereas for rows, the second value specifies the row number, rather than the column number. So, +INDEX(A1: F1, 4) would present the value in D1 because that’s the fourth column.
By selecting the whole table as the range, you can have free reign to navigate your way around every cell using coordinates. However, without MATCH, you can’t use INDEX unless you already know the coordinates. And that’s where we come in:
Match saves you time by counting columns for you, or rather, it looks for an actual value in the column and row and tells you its location, so there’s no need to do any counting at all!
The syntax for MATCH looks like this:
=MATCH(search_key, range, [search_type])
The breakdown:
- Search_key = the value you’re searching for. For digital marketing this will usually be “Clicks”, “Impressions”, “CTR” etc.
- Range = the range you want to look for the value in. It has to be one-dimensional (it has to contain either one row or one column). This basically means a straight line and looks like: A2:D2 or D4:D16
- [search type] = an optional part of the search, this allows you to select the type of search you want it to do. Usually, its best to set this to “0” which equates to an exact match.
For example:
If you’re looking for the click-through rate which is in column E, you’d first select the search key by writing CTR in the search_key bit, or just E1. Next you need to select the range. To do this you’d select the header values in your table from A1 to G1. Then, to get an exact match, you’d type “0” and close it all off with a bracket.
This match function is really useful for finding values if you’ve rearranged, deleted or inserted columns into your spreadsheet.
INDEX MATCH and PPC
You can use INDEX MATCH for your PPC campaigns to conduct performance reviews as it allows you to look up values across multiple campaigns.
For example, you can look up the total number of conversions across all campaigns. You would do this by using INDEX to select the table, then writing “1” as a placeholder in both row and column. This “1” gives the value of the cell in the top lefthand corner of the table – in this case it would be A1.
Then, you’d replace each of those 1’s with a MATCH function. Because the first “1” corresponds to the row, this can be used to create a MATCH function that selects the row containing the total.
What’s more, with INDEX MATCH, you don’t even need to type “conversions”. You could even just select the cell containing the value you want to look up because it’s only the search value itself that has to be in the lookup range.
This also applies for rows. So instead of wasting time searching or counting for the values you want, instead you can just type in the Campaign or Ad Group you’re looking for and be instantly presented with the metrics that you want.
Spreadsheet success
As with most PPC processes, a strategy that requires less time but supplies optimal results is the best way for you to achieve a higher ROI. By making use of INDEX MATCH to search for and select the values that help you quickly identify how well your PPC campaign is converting, you can easily see which aspects are the most successful, and which need a little more work. All without wasting hours perusing through spreadsheet columns that would bore the eyes off of even the most spreadsheet scintillated amongst us.
