HELP Top 3 Dynamic List Using =query?

positronicfuture

New Member
Joined
Nov 17, 2017
Messages
1
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!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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 :biggrin:

=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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top