Formula needed to Lookup Deal criteria

[MarkCBB]

Hi there formula Pros,

I think I finally have a challenge worthy of the formula Pros, (I Think)

I have a database of outlets that have certain criteria, I.e. Channel, Rep type, as well as date of activation.

Here is the layout of the Outlet database:
|Store name | Channel | Rep type | Date of Activation |

I also have a deal sheet on another worksheet that have the same criteria, expect the deal sheet has a start date and an end date.
From the Outlet database sheet, based on the criteria I need to find the relevant deal.

i.e. is the outlet the same channel and same rep type, and is the date of activation in-between the start and end date of the relevant deal, and then assign the correct value of deal.

Here is the layout of the Deal sheet:
|Deal name | Channel | Rep type | Start date | End Date | Value of deal |

There is a lot more logic to this, but If I see how this bit is done, I should be able to figure out the rest.

This seems really simple to me, but I can’t seem to get the date range working,
This is how I tried to approach it; I inserted helper columns into each database with a concatenation of Channel and rep Type, and then used IF logic against the dates, but no luck.


let me know if you need any more info.

Regards,
Mark Blackburn

 

[T. Valko]

Hi there formula Pros,

I think I finally have a challenge worthy of the formula Pros, (I Think)

I have a database of outlets that have certain criteria, I.e. Channel, Rep type, as well as date of activation.

Here is the layout of the Outlet database:
|Store name | Channel | Rep type | Date of Activation |

I also have a deal sheet on another worksheet that have the same criteria, expect the deal sheet has a start date and an end date.
From the Outlet database sheet, based on the criteria I need to find the relevant deal.

i.e. is the outlet the same channel and same rep type, and is the date of activation in-between the start and end date of the relevant deal, and then assign the correct value of deal.

Here is the layout of the Deal sheet:
|Deal name | Channel | Rep type | Start date | End Date | Value of deal |

There is a lot more logic to this, but If I see how this bit is done, I should be able to figure out the rest.

This seems really simple to me, but I can’t seem to get the date range working,
This is how I tried to approach it; I inserted helper columns into each database with a concatenation of Channel and rep Type, and then used IF logic against the dates, but no luck.


let me know if you need any more info.

Regards,
Mark Blackburn

What version of Excel are you using?

 

[MarkCBB]

Excel 2010

 

[T. Valko]

Excel 2010

Ok, try something like this...

Written in "pseudo code".

=SUMIFS(Value of deal range,Deal sheet channel range,some channel,Deal sheet rep type range,some rep type,Deal sheet Start Date range,"<="&Date of Activation,Deal sheet End Date range,">="&Date of Activation)