Looking up multiple columns for results

[EMMAPAULIN]

Hello,

I am creating a spreadsheet that needs to look up results in another workbook. It needs to look up multiple cells to come up with a result.

I have mocked up one for this question.

I have two workbooks.

Item. 2 - Raw Data
Item. 2 - RPL Example Workbook

I need to RPL Example Workbook to pull data from Raw Data, it cannot be a combined workbook. It needs to be linked.

I would like to enter the SAP Number into the RPL Example Workbook, and it to pull information across, however the Raw Data doesn't have the information laid out in the same format as the RPL Example Workbook.

Raw Data has it laid out like, each induction completed has it's own line.

In RPL Example Workbook, I want to put in the SAP number, and it will generate the person's name and then it the columns highlighted in green, I want it to only show the date that particular induction was completed. Then in the red column for induction completed, I want it to automatically show "Completed" or Required. Completed if all dates are in the inductions fields, and Required highlighted in red and bold if even 1 date is missing in the induction fields.

Can anyone help me?


RAW DATA

SAP No.	Name	Date	Induction Code	Induction Course
583012	Emma Lemon	23/01/15	2040023	General Induction
583012	Emma Lemon	23/01/15	2040024	Site Induction
583012	Emma Lemon	07/02/15	2040025	Alcohol and Other Drugs
583012	Emma Lemon	23/01/15	2040026	JHA and Take 5 100%

<tbody>
</tbody>

RPL Example Workbook

SAP No.	Name	Induction Completed	General Induction	Site Induction	Alcohol and Other Drugs	JHA and Take 5 100%

<tbody>
</tbody>

Many thanks in advance.
Emma.