Formula to Scan Seperate Sheet and Fill Information

Occasional Visitor

I am trying to merge information from two sheets in a single workbook.


Currently, there is information for all of the employees that I work with in a "Sheet1" and information of only those who have received promotions in a "Sheet2."  I hope to insert a formula into column A of Sheet1 that will scan through Sheet2 to see if the employees' information is listed.  If it is, "Yes" will be inserted into the cell, and if it isn't, "No" will be inserted into the cell.  In addition, if "Yes" was inserted into the cell, I hope that in the adjacent column (I assume this would be a separate formula), their promotion date could be inserted.

For example, referring to the attached sample sheet with random information, since "Smith, Kevin" is listed in both Sheet1 and Sheet2, I hope that the first red cell would be filled with "Yes" and the following red cell would have "01/15/2019" inserted.  And, since Sanders, Thomas is listed ONLY in Sheet1, I hope that the yellow cell would be filled with "No."

Thank you so much for your help!

1 Reply

Hi, Have attached an updated sheet with a formula that will work for you