Merging INDEX & SUMPRODUCT formula

Hi Team,

I'm trying to develop an universal formula instead of having 4 different ones.

Please refer to the attached workbook as I'd like this formula to be applicable for all four departments but I'm not sure how to get it to search across different ranges.

For example, Mark's assigned to Dept 1 but I'd need to see his item (Column C) even though he's scheduled for any other departments.

Formula:

Please Login or Register  to view this content.

One other thing would be if there is a way of identifying a clash if someone is double booked for a day?

Thanks for all your help.

Cheers,
Kapi

Hi all-
Here's a solution that uses the AGGREGATE function, which can mimic other functions - in this case SMALL(array,k) - with flexible error handling.
In B13, copied across and down:

Formula:

Please Login or Register  to view this content.

Note the use of a Named Range called Data; it refers to Sheet1!A5:Q8 (the data, obviously). This makes the formula a little shorter and way more readable, especially if the data is on another sheet, but just in case you really hate Named Ranges, here's the same formula with literal references.

Formula:

Please Login or Register  to view this content.

SChkpic.jpg

It took me awhile to find it; your formula had a typo, so the finish date test was looking at the wrong cell. ("$A13" should have been "$A3")

Formula:

Please Login or Register  to view this content.

I noticed you tried SINGLE(array) in a couple of spots as a fix, but both of those clauses yield single values anyway. Here's a working version:

Formula:

Please Login or Register  to view this content.

ps. This is NOT an array formula.

You're welcome, happy to help!