Lookup from multiple criteria, including potential partial matches

trancer78

New Member
Joined
Jan 15, 2012
Messages
5
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),"")

ADEFGH
Emp Dept.DateCodeProj NameTotalID
HTCS1/25/181SAAS VAR1.2514268
MRS1/25/181SAAS VAR10.001002
OSD1/25/181SAAS VAR10.003103
MRS1/24/181Room Type Audit8.001003

<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>
</tbody>

Project Summary tab:
BEFKLMNRS
Project NameCodeStartHTCSOSDMRSPSDEnd DateConcatenate
Room Type Audit101/23/18 MRS 2/1/2018MRS
SAAS VAR101/25/18HTCSOSD 1/25/2018HTCSOSD

<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>
</tbody>

<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>
</tbody>
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top