I am sure you have heard of or even used a v-lookup function, this function became the de-facto lookup function in practically the stone ages. I have some serious problems with this function, it requires setting your data up correctly (probably a good thing but lets be honest we are all way too lazy to do that), you have to count your rows or have a super human ability to just inherently know that column AA is 12 columns away from P. On top of that you have to have your criteria column on the left. Who has time for that? Thus I propose a better function the index(match) function.
The basics of the function are as follows =index("column you want to bring back",match("cell you want to look up data for","column your criteria is in","0 for exact match")). Confusing I know so I will explain below in broken down parts.
Look at the excel table below, we have some data in column A through C with A being the date, B ad name, and C clicks. D is where we will be placing our campaign name. In the marketing world a common task would be to flag your data based on campaigns so you can get a wholistic view. Ideally you will have in another tab in excel you ad names already matched to your campaign. But for this small example I placed it in the same sheet in columns F and G. The goal is to take that list of campaign names and apply it to your raw data.
In order to do this you would use Index(match()).
Step 1: Index("data you want")
In our example we have a list of campaign names and ads, in column G we have the campaign name and column F the ad name.
We also have our data we want to append our campaign names to (columns A through D).
In cell D2 we type =index(G:G,match(
Step 2: we add to our match function what the criteria is we want to match, in our case A2, then we want to add what column we want our data to match from column F in this case.
Our formula at this point looks like =index(G:G,match(A2,F:F
While this next part I have not found a use for why you wouldn't want an exact match (0) but you can choose 1 which gives you the next closest one if no exact match is chosen.
Our final formula looks like
=index(G:G,match(A2,F:F,0)).
Now drag this to the bottom of your data table and you have successfully appended campaign names to your data! Pretty neat right, well I think it is at least. While this may not seem like a practical reason to do this as a lot of platforms give you campaign names, but how many of us are in control of naming each of the campaigns in each platform, or we get data from different vendors who use their own campaign name. Now we can combine all of our campaign stats into one summary table. Much easier than adding everything up individually.
Next up combining your index matched data with sumifs to really speed up your data collection/aggregation!
Enjoy!