Lookup from multiple criteria, including potential partial matches

[trancer78]

Hi Everybody,

Hoping somebody is able to help me with this formula. We have multiple departments and project codes so the actual file is much more comprehensive. I've scaled back for the sake of this example.

Currently the formula below looks up the project code and the date and compares that to data on the 'Project Summary' tab, returning the project name associated to that code for that date range. So far, so good.

Issue I am running into is that a project code could be used simultaneously in different departments for separate projects. So I need to add another criteria, that matches the value in column A to the department assigned the project (columns K:N on the 'Project Summary' tab). Projects could be assigned to just one department or multiple.

I tried doing a concatenate formula across each of the departments in hopes of finding the department as part of the value of that field to create the match, but I haven't been able to successfully add it to the current formula.

Any help would be appreciated. This is the last formula to figure out before the report is fully functional.

Current formula in column F:
=IFERROR(LOOKUP(2,1/('Project Summary'!$E$2:$E$296=E2)/(D2>='Project Summary'!$F$2:$F$296)/(D2<='Project Summary'!$R$2:$R$296),'Project Summary'!$B$2:$B$296),"")

[TABLE="width: 471"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]Emp Dept.[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Code[/TD]
[TD="align: center"]Proj Name[/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]ID[/TD]
[/TR]
[TR]
[TD="align: center"]HTCS[/TD]
[TD="align: center"]1/25/18[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]SAAS VAR[/TD]
[TD="align: center"]1.25[/TD]
[TD="align: center"]14268[/TD]
[/TR]
[TR]
[TD="align: center"]MRS[/TD]
[TD="align: center"]1/25/18[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]SAAS VAR[/TD]
[TD="align: center"]10.00[/TD]
[TD="align: center"]1002[/TD]
[/TR]
[TR]
[TD="align: center"]OSD[/TD]
[TD="align: center"]1/25/18[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]SAAS VAR[/TD]
[TD="align: center"]10.00[/TD]
[TD="align: center"]3103[/TD]
[/TR]
[TR]
[TD="align: center"]MRS[/TD]
[TD="align: center"]1/24/18[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Room Type Audit[/TD]
[TD="align: center"]8.00[/TD]
[TD="align: center"]1003[/TD]
[/TR]
</tbody>[/TABLE]

Project Summary tab:
[TABLE="width: 717"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]S[/TD]
[/TR]
[TR]
[TD="align: center"]Project Name[/TD]
[TD="align: center"]Code[/TD]
[TD="align: center"]Start[/TD]
[TD="align: center"]HTCS[/TD]
[TD="align: center"]OSD[/TD]
[TD="align: center"]MRS[/TD]
[TD="align: center"]PSD[/TD]
[TD="align: center"]End Date[/TD]
[TD="align: center"]Concatenate[/TD]
[/TR]
[TR]
[TD="align: center"]Room Type Audit[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]01/23/18[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]MRS[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]2/1/2018[/TD]
[TD="align: center"]MRS[/TD]
[/TR]
[TR]
[TD="align: center"]SAAS VAR[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]01/25/18[/TD]
[TD="align: center"]HTCS[/TD]
[TD="align: center"]OSD[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1/25/2018[/TD]
[TD="align: center"]HTCSOSD[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 893"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col span="4" style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]