VBA: Vlookup and Separate

[unknownymous]

Hi Guys,

I'm trying to create a macro but having a hard time creating as I'm more on recording and there are some conditions that needs to be met.

I have an excel file with 3 tabs:

Sheet 1: Master Tab

ID	Name
001
000
005

<tbody>
</tbody>

Sheet 2&3: Source 1 & 2 (Format is the same but the data is different - see below sample)

ID	Name	Score	Note
001	Jack	88	Student
002	Sheena	85	Student
004	May	70	Teacher
003	John	0	Student
005	Sam	0	Student

<tbody>
</tbody>

Task:
1. I need to look up both Sheet 2 & 3 against Sheet 1 by using ID column as ref.
2. If the cell is not in the lookup range and the score is zero, it will be put under the "ZERO" section.
3. If the cell is not in the lookup range and there is a score, then the row will be highlighted and another sheet will be created name "Add" (should be added in the Master tab) which contains the ID, Name & Source Tab Sheet Name (see below).
4. If the Note Column is "Teacher", either there score there is a score on none, it will be put under the "EXCLUDED" section
5. See below Final Result

ID	Name	Score	Note	LookUp Result
001	Jack	88	Student	001
005	Sam	0	Student	005
002	Sheena	85	Student
ZERO
003	John	0	Student
EXCLUDE
004	May	70	Teacher

<tbody>
</tbody>

"Add" Tab

ID	Name	Source Tab
002	Sheena	Source 2

<tbody>
</tbody>

Will appreciate any help you could give.


Thank you!