Index Matching + Data ranges inside a MINIFS()

[cbmxd]

Hey there Excel Wizards, got a tricky problem here I couldn't find a solution for. I'm using a data set for campaign spend on a daily basis. In short, I need to pull in the budget of each campaign to each of my rows in my data set. I can match on campaign and advertiser name without a problem, but because there can be multiple flights of a campaign I also need to match on a date. Tricky thing is the majority of dates will not be an exact match.

Below are two screenshots of my sheet; one is a table with my data and calculated values (column H, highlighted in orange, is where my issue is), the other is where the flight data lives in Campaign Flights!B6:F1007. If it works properly, the value in cell H2 should be $10,000 and H3 should be $15,000. The formula I have been using in column H is as follows:

=IFERROR(IF(ISBLANK([@Advertiser]),"",INDEX('Campaign Flights'!$B$6:$F$1007,MATCH(1,([@Advertiser]='Campaign Flights'!$B$6:$B$1007)*([@Campaign]='Campaign Flights'!$C$6:$C$1007)*([@Date]-MINIFS('Campaign Flights'!$E$6:$E$1007,[@Advertiser],'Campaign Flights'!$B$6:$B$1007,[@Campaign],'Campaign Flights'!$C$6:$C$1007,[@Date],">="&'Campaign Flights'!$E$6:$E$1007)+1=[@Date]-'Campaign Flights'!$E$6:$E$1007+1),0),3)),0)

The key in this equation is the MINIFS() I'm using since one of my ifs is referencing a range of cells instead of a singular cell or value. For reference, before I tried index matching using date I was receiving the correct values using this formula:

=IFERROR(IF(ISBLANK([@Advertiser]),"",INDEX('Campaign Flights'!$B$6:$F$1007,MATCH(1,([@Advertiser]='Campaign Flights'!$B$6:$B$1007)*([@Campaign]='Campaign Flights'!$C$6:$C$1007),0),3)),0)

Multiple campaign flights mess this up pretty bad. Open to ideas/thoughts/alternative solutions!

 

[Alex Blakenburg]

Welcome to the Forum.
You will generally get a better and faster response if you use XL2BB to provide a sample of your data rather than a picture. It often takes longer to set up the test data than to provide a possible solution.

See if this works for you:

20221201 Lookup Dates cbmxd.xlsx
	A	B	C	D	E	F	G	H
1	Date	Advertiser	Advertiser ID	Campaign	Campaign ID	Advertiser Currency Code	Advertiser Cost (Adv Currency)	Campaign Budget
2	1/01/2023	Example Advertiser Name	Advertiser ID	Example Campaign Name	Campaign ID	USD	333.33	$10,000
3	5/02/2023	Example Advertiser Name	Advertiser ID	Example Campaign Name	Campaign ID	USD	333.33	$15,000
Spend

Cell Formulas
Range	Formula
H2:H3	H2	=XLOOKUP( 1, ( 'Campaign Flights'!$B$6:$B$1007 = [@Advertiser] ) * ( 'Campaign Flights'!$C$6:$C$1007 = [@Campaign] ) * ( 'Campaign Flights'!$E$6:$E$1007 <= [@Date] ) * ( 'Campaign Flights'!$F$6:$F$1007 >= [@Date] ), 'Campaign Flights'!$D$6:$D$1007, "" )

In case others want to provide a response here is the other sheet as an XL2BB

20221201 Lookup Dates cbmxd.xlsx
	B	C	D	E	F
6	Advertiser	Campaign Name	Budget	Flight Start Date	Flight End Date
7	Example Advertiser Name	Example Campaign Name	$10,000	1/01/2023	31/01/2023
8	Example Advertiser Name	Example Campaign Name	$15,000	1/02/2023	28/02/2023
Campaign Flights

XL2BB Instructions
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[cbmxd]

Alex, you are a saint that works! Really appreciate the help here, hadn't used an XLOOKUP() before :D

Thanks for the note about the forum pages as well. Newbie me wasn't sure the best place to post. Thanks again!

 

[Alex Blakenburg]

You're welcome. Glad to have been able to help.