I want a formula that returns the top 10 keywords (by clicks) for the last week from a data set.

This works, but returns duplicate values;

INDEX(List of keywords,MATCH(LARGE(IF(date column="This Week",number of clicks),1),number of clicks,0),0)

I've done something similar before where the formula in the next row will check the first and return the next different value, and so on, but I can't get it to work for this.

Any ideas? No VBA please.

  • 1
    Why not use pivot table? – Máté Juhász Sep 30 '16 at 15:39
  • Change the 1 to ROW(1:1) that way as it copies down it will grab the second then the third. I would also put the IF() in the second driterion of the MATCH: =INDEX(List of keywords,MATCH(LARGE(IF(date column="This Week",number of clicks),ROW(1:1)),IF(date column="This Week",number of clicks),0),0) This way you do not return matches that are not filtered. – Scott Craner Sep 30 '16 at 15:44
  • Assume your data is named "raw", step 1 use countif with your list of keywords, This gets how many times each keyword used. If this is in a table, you could just sort by the countif column. otherwise, list column someplace is the keywords, the second column is the countif and then the next column is a repeat of the keywords. The countif column is named cnt. The countif and the repeat column are named proc. then somewhere where you have 1->10 =vlookup(large(cnt,1),proc,2,false) gives largest, the same formula with cnt,2 gets the 2nd largest, and so on – bvaughn Sep 30 '16 at 16:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.