Query Dataset and Return Value Based on Date Range

[bigscreentv]

Hi,

I have a problem that I'd really like some help with if possible!

I have a dataset that looks like the following:

Start Date End Date Campaign Name Campaign Type
1/9/17 1/10/17 Test Campaign Acq
1/7/17 31/7/17 Affiliate Campaign Aff
1/7/17 14/7/17 Other Campaign Oth

I would like to write a formula/custom function that looks up the date range, then the campaign type and returns a unique list of campaign names. Obviously the dataset is much bigger than the one above, and this report needs to be done weekly so any help would be greatly appreciated.

Also, if a custom function will do the trick, i am building this report in Google Sheets, so VBA won't be appropriate unfortunately.

Thanks,

Bigscreentv

[FlameRetired]

Hi Bigscreentv. Welcome to the forum.

This suggests a large data set. I would recommend using a helper column that indexes the row numbers of qualifying Campaign Names. This way you can reference the target date and target type such as in G1:H1 below.

	A	B	C	D	E	F	G	H
1	Start	End	Name	Type	hlpr		15/7/2017	Aff
2	1/9/2017	1/10/2017	Test Campaign	Acq			Names
3	1/7/2017	31/7/2017	Affiliate Campaign	Aff	3		Affiliate Campaign
4	1/7/2017	14/7/2017	Other Campaign	Oth

Then use this helper column formula in E2 filled down.

Please Login or Register  to view this content.

And this final output formula in G3 filled down until you get blanks.

Please Login or Register  to view this content.