Complex INDEX MATCH - Need assistance

Monica,

So, if Sheet1

and Sheet2

in C2 here is array formula (Ctrl+Shift+Enter)

=IFERROR(
 INDEX(Sheet1!$C:$C,
 MATCH(1,
 (Sheet1!$A:$A=Sheet2!$A3)*(ISNUMBER(FIND(Sheet2!B$1,Sheet1!$B:$B))),0),1),0)

IFERROR returns zero if nothing is found

ISNUMBER(FIND... returns array with TRUE (1) if B1 is found and 0 if not

Before it simular array for names, both of them with AND condition show us where are the records with both the name and code are as in Sheet2.

When we copy down and to the right the cell with formula.

Above formula is not good from performance point of view. Better to limit the range (e.g. $A$1:$A$100, etc.) and even more better to transform the range in Sheet1 into the Table. That gives us the array formula

=IFERROR(INDEX(Table1[Value],MATCH(1, (Table1[EE Last Name]=Sheet2!$A2)*(ISNUMBER(FIND(Sheet2!B$1,Table1[Code]))),0),1),0)

Sample files are attached