Excel: Complex Lookup + 'If Cell Contains'

Question

Example of sheet 1

Where I have repeated entries for the employee. Most employees will have two different shift types but some might have one. The profile contains days of the week and each day of the week is unique to one profile (i.e. no overlapping days between profiles).

employee_name / shift_type / profile 
mr_a / type_a / Saturday_Sunday
mr_a / type_b / Monday_Tuesday_Wednesday
mr_a / type_b / Thursday_Friday
mrs_b / type_a / Sunday_Monday_Tuesday
mrs_b / type_b / Wednesday_Thursday_Friday

Example of sheet 2 Where I have a log of each employee, the shift type and the specific day of the week.

employee_name / shift_type / day_of_week
mr_a / type_a / Saturday
mr_a / type_b / Wednesday
mr_a / type_b / Friday

Desired Results: What I would like to do is successfully match the records from sheet 2 against sheet 1. I want to do is match the profile where:- match employee_name and shift_type are on an 'exact' basis and the profile returned matches the day_of_the_week on a non-exact basis (only a partial match based on the part of the string)

Like so:

employee_name / shift_type / day_of_week / matching_profile
mr_a / type_a / Saturday / Saturday_Sunday
mr_a / type_b / Wednesday / Monday_Tuesday_Wednesday
mr_a / type_b / Friday / Thursday_Friday

What I have tried: I have tried using array formulae and have been able to solve both halves of the problem. I can get the multiple-matching conditions using multiple lookup criteria in an index_match. However, I don't know how to then combine that to find the match where the cell contains part of the text (i.e. the weekday).

Something like this (but which isn't working):

=INDEX('sheet1'!profile,MATCH(1, (employee_name='sheet1'!A:A) * (shift_type*'sheet1'!B:B) * (TRUE,ISNUMBER(SEARCH(day_of_week,'sheet1'!profile),0))

Thank you for any help!

Answer 1

Here is a solution I was able to leverage using Wildcards. As a caveat for anybody using this, be mindful that this is basically doing partial matches. However, given that the employee_name and shift_type in my example are unique identifiers, this didn't bother me. This would be an issue for anybody who is matching values which might be sub-strings.

=INDEX(sheet_1!C:C,MATCH("*"&sheet_1!A2&"*"&"*"&Sheet1!B2&"*"&"*"&Sheet1!C2&"*",sheet_1!B:B&sheet_1!D:D&sheet_1!C:C,0))

Answer 2

Try this one, it's an array formula, so enter with CNTRL + SHIFT + ENTER:

{=OFFSET(Sheet1!$C$1,(MATCH(1,--(Sheet1!A:A=Sheet2!A3)*(Sheet1!B:B=Sheet2!B3)*NOT(ISERROR(SEARCH(Sheet2!C3,Sheet1!C:C))),0))-1,0)}

I'll explain this from the inside out, it makes more sense that way:

(Sheet1!A:A=Sheet2!A3)*(Sheet1!B:B=Sheet2!B3)*NOT(ISERROR(SEARCH(Sheet2!C3,Sheet1!C:C)) is your logical test, it's checking whether B2 is found in Sheet1 B:B and returning 1 where it is and 0 where it isn't. Same thing with A2 and A:A. NOT(ISERROR(SEARCH(Sheet2!C2,Sheet1!C:C))) is checking each cell in C:C to see if it contains a partial match of the text in C2. This returns an array of 0's and 1's.

MATCH(1,(array from above),0) tells you where in the array the first 1 occurs. It returns a single number.

OFFSET(Sheet1!$C$1,(Number from above)-1,0) takes the value from Sheet1, column C, and uses the number from above to find the row.

This formula will return the "Weekday_Weekday_Weekday" in column C if there is a match, and #N/A if there is not. If C2 is left blank it will return the "Weekday_Weekday_Weekday" anyways (error only occurs if weekday doesn't match).