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

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.