Excel lookup formula when using multiple pivot tables

I am new to advanced Excel formulas and would appreciate any assistance with this problem. I have 3 pivot tables in one Excel worksheet (Sheet 2). In cell $A$11 of Sheet 1, I would like to take the partial text string (Employee ID) and locate it in Sheet 2. If found, return the Employee ID and Name in cell B11 and B23 in Sheet 1. Please refer to the attachment.

Thanks in advance for viewing my post.

Originally Posted by sweetpea12

I am new to advanced Excel formulas and would appreciate any assistance with this problem. I have 3 pivot tables in one Excel worksheet (Sheet 2). In cell $A$11 of Sheet 1, I would like to take the partial text string (Employee ID) and locate it in Sheet 2. If found, return the Employee ID and Name in cell B11 and B23 in Sheet 1. Please refer to the attachment.

Thanks in advance for viewing my post.

Correction: If found, return the Employee ID and Name in cell B11 and B12 in Sheet 1.