Formula help please.

[Seyk1701]

For context I have two workbooks which contain the same problem types. Workbook 1 contains the site size (large or small), and workbook 2 contains expected duration for a specific site type based on the size of the site.

What I'm looking for is a formula that will match the problem types and look at the site size in workbook 1. So if problem type matches + site size is small, the formula should return duration for small site, or else it should return the duration for a large site (or #NA)

 

[dreid1011]

It would be helpful if you provide sample data from each workbook and your expected results.

 

[Seyk1701]

Sorry yeah here:

Workbook 1

Site size	Concat	Duration (based on site size. If site <> small default large duration)
Large	EVRiLIFT EQUIPMENTPASS LIFTPassenger Goods Lift LOLER Inspection- 6 Monthly
Small	A.S.Watson(Health&BeautyUK)ELECTRICALPOWERHV Sub-Station (Visual Inspeciton Only) - Monthly - Depots Only

Workbook 2

Concat	Large site duration	Small Site Duration
A.S. Watson (Health & Beauty UK) LimitedACCESSBARRIERSFault Or Failure	1	0.5
The Arch CompanyStatutory inspections / SurveysFire SafetyWeekly Testing of Fire Alarm System	2	1

 

[dreid1011]

Assuming "Concat" is the problem type? How much of this is supposed to match between sheets? Partial match? Exact match? I may not be the person to help if it's going to be a partial match on both ends. And even the parts that I would assume match are not consistent. "A.S. Watson (Health & Beauty UK)" vs "A.S.Watson(Health&BeautyUK)".