Joining two Excel files, where additional fields need to be added based on inputs

[JoshuaElphee]

I have a need to join two workbooks (common Key will be "Employee ID"). 

In workbook 1, I show employee ID and their paid amount.

In workbook 2, I show employee ID and 7 categories which are listed "Yes" or "No"

The resulting workbook I need, is workbook 1, with 7 additional columns - where each field marked "Yes" in workbook 2 under an employee ID for a given column shows the paid amount, from workbook 1 within that cell.  For example, if columns 1,3, and 6 of workbook 2 show "Yes" for a given employee ID, and workbook 1 shows $1000; I will need the final workbook to have the equivalent columns 1, 3, and 6 show $1000.

I have been trying to use the Join function of course, which brings in the "Yes" or "No" from workbook 2, however I am unable to identify how I can replace those showing "Yes" with the $ value from workbook 1.

[binu_acs]

@JoshuaElphee One way of doing this is by transposing the workbook data and join with workbook 1 and cross tab the result