HELP Top 3 Dynamic List Using =query?

[positronicfuture]

Hi all,

I am creating a report template and I am having issues with one formula.

I have 2 sheets, one has the report, one has the data - this one is called FB_POST

On the data sheet, column B cells will say either "This week", "Last week" or be blank.

On the report sheet I want a formula that will display the top 3 posts by organic reach (column I of the data tab) that have "This week" written in column B.

So the logic would be: On a cell of the the report sheet, show me the top 3 posts from the data sheet (this will look like a little table with 3 columns for link, content and value - as we are talking about social posts) by organic reach (column I) in descending order if column B of the data sheet says "This week".

I've made some progress with a =query function but cannot exactly get what I need - sure I am overcomplicating things.

Any help would be hugely appreciated!

 

[bellman101]

Hi there,

Think I have what you're after. Maybe a little clunky, but it does the trick.

So just to be thorough;


=VLOOKUP("This Week",FB_POST!B:I,8,0)
should get you the top one

=VLOOKUP("This Week",INDIRECT("FB_POST!B"&MATCH("This Week",FB_POST!B:B,0)+1&":I"&COUNTA(FB_POST!A:A)+0),8,0)
should return the corresponding erm... organic reach.

=VLOOKUP("This Week",INDIRECT("FB_POST!B"&MATCH("This Week",INDIRECT("FB_POST!B"&MATCH("This Week",FB_POST!B:B,0)+1&":B"&COUNTA(FB_POST!A:A)+0),0)+MATCH("This Week",FB_POST!B:B,0)+1&":I"&COUNTA(FB_POST!A:A)+0),8,0)
gets you number 3.

This is all on the assumption that column A has stuff in every row, and that I've got your columns right of course.

Cheers
JB

 

[bellman101]

Oh, sorry - it also assumes there are no blank rows at the top, but if there are, where I've put "COUNTA(...)+0)", just change that 0 to number of blank rows. 2 places in 3rd formula.