I have two Excel workbooks with 1 sheet in each. Workbook1 Addresses contains: A – Full Name B – Address C - Zipcode D – Employee ID

Workbook2 A – First Name B – Last Name C – Zipcode

How can I run a lookup from Workbook2 that will return the Employee ID when Zipcode in both sheets match and [First Name] in contained in [Full Name] And [Last Name] is contained in [Full Name}?

CodePudding user response：

This can be done.

Define the table array to reference the data in the other workbook, or even define it with a name, see Insert: Names: Define.

You can also consider putting both sheets in the same workbook. This can avoid issues of moving one of them.

CodePudding user response：

Formula to get the ID

=INDEX([WB1.xlsx]Sheet1!$A$2:$D$20;MATCH(1;([WB1.xlsx]Sheet1!$C$2:$C$21=C2)*(ISNUMBER(SEARCH(TRIM(A2);[WB1.xlsx]Sheet1!$A$2:$A$20)))*(ISNUMBER(SEARCH(TRIM(B2);[WB1.xlsx]Sheet1!$A$2:$A$20)));0);4)

Example Workbooks: