Support with planning resource - lookup and return string for multiple date ranges

[WhitneyMoore]

Good afternoon!

I'm seeking some support please with using excel for resource planning.

I'd like to use excel to time block dates resources are unavailable. My resources are labelled BP_01 to BP_09 in the attached. I have broken my current progress in the first thick outline table with the desired outcome in the second thick outline table. The desired result looks up a string based on the resource number and returns the string; it also recognises multiple strings for the same resource (for example BP_01 which is used between 5/10 & 6/10 then again between 19/10 & 26/10 using different string lookups).

Is this possible in excel? Could you please assist with the formula / VBA coding to develop this?

Thank you so much!

Formula in D6:AC11 

=IF(AND(D$5>=(INDEX($C$21:$E$43,MATCH($C6,$C$21:$C$43,0),2)),D$5<=(INDEX($C$21:$E$43,MATCH($C6,$C$21:$C$43,0),3))),"x","")

Formula in D13:AC13

=SUM(D6:D11)

[#2]

Yes, it is possible to achieve your desired outcome in Excel. You can use a combination of Excel functions to look up a string based on the resource number and return the string.

It can also recognize multiple strings for the same resource.

The formula you’re currently using in D6:AC11 is a good start. It uses the INDEX and MATCH functions to look up the start and end dates for each resource in the range C21:E43.
If the date in row 5 is within this range, it returns “x”, otherwise it returns an empty string.

However, this formula only works for the first match it finds for each resource. If a resource is used at different times with different strings, this formula won’t return all of them.

To handle multiple strings for the same resource, you could modify your formula to something like this:

=IF(SUMPRODUCT(($C$21:$C$43=$C6)*($D$5>=INDEX($C$21:$E$43,MATCH($C6,$C$21:$C$43,0),2))*($D$5<=INDEX($C$21:$E$43,MATCH($C6,$C$21:$C$43,0),3)))>0,"x","")

This formula uses SUMPRODUCT to sum up all instances where the resource matches, and the date in row 5 is within the start and end dates.
If this sum is greater than 0, it means there’s at least one match, so it returns “x”.

Please note that this is just a suggestion and might need some adjustments based on your specific data layout and requirements12.
If you’re not familiar with these functions or if you need further assistance, I would recommend reaching out to someone with expertise in Excel formulas or VBA coding.

I hope this helps! Let me know if you have any other questions.

[#3]

@suraj786 ah thank you! I always forget the power of SUMPRODUCT. The final solution I went with is below, thanks for the prompt and support 

=IF(SUMPRODUCT(($B$70:$B$77=$L19)*(T$8>=$F$70:$F$77)*(T$8<=$G$70:$G$77)*($H$70:$H$77))>0,SUMPRODUCT(($B$70:$B$77=$L19)*(T$8>=$F$70:$F$77)*(T$8<=$G$70:$G$77)*($H$70:$H$77)),"")