# Formula to pull Top 10 Keywords based on values in a separate column

1. ## Formula to pull Top 10 Keywords based on values in a separate column

Hi everyone,

I'm trying to work on a table for Search Engine Marketing that would give me the top keyword based on a value in a different column. One of the issues I've had with the index/match/large functions is that I have daily data ranging all of 2017 so far and it will return the keyword based on the highest single instance of click volume through the entire year; I'd like for it to see every instance of the keyword within a set period (aka a Month) and determine the highest aggregate click volume and return that keyword...I hope this make sense, I'll break it out into a simpler table version of what I'm working with:

Basic version of the table I'm trying to build, Column A is where I need a formula to extract a keyword based on its aggregate click volume within a single month, Columns B & C use sumifs to pull the impressions and click data from the keyword in column A based on a defined month:
excelexample1.JPG

All of this data is being pulled from a raw data set turned into dynamic table, so I can pull metrics based on labels instead of cell ranges (i.e. if I want to sum all clicks =sum(RawData[Clicks])).

So what I'm looking for is a formula that will go into the raw data table, sum the clicks from column C per keywords in column A and Month in column D, then return the keyword from column A of the raw data that has the highest summed clicks within the Month defined in column D. Below is an example of the column headers for my raw data:
excelexample2.JPG

Am I getting too complicated with this? It seems like a simple enough idea in my head, but I'm not sure if I can accomplish this with my current data set up and a single formula.

Thanks!

2. ## Re: Formula to pull Top 10 Keywords based on values in a separate column

Hello dyljenx and Welcome to Excel Forum.
It would help us to attempt to help you if you could upload a sample of the raw data table and the results that you would expect. You'll need to manually input results so that contributors can attempt to write formula/code that will replicate those results.
To upload a sample workbook click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window. Make sure there is enough data to demonstrate your need. Remember to desensitize the data if necessary.
Let us know if you have any questions.

There are currently 1 users browsing this thread. (0 members and 1 guests)