Lookup Multiple Tables, match, and extract relevant data

Can anyone help me with this please..

I have a worksheet (Sheet1) listing
A) Employee ID number
B) Flight No.
C) Date of Flight
D) Day of Week
E) Type of Flight

The next worksheet (Sheet2) contains
A) Flight No.
B) Weekdays (Sunday to Saturday) listing the type of flight (Long or Short) against each day. Denoted by "L" or "S". "x" means no flights on that day of the week.

When an employee on Sheet1 requests for a Flight No. on a give date/day, I would like the type of flight ("L" or "S" or "X") to appear in E2. Which of course is extracted from the relevant cell in Sheet2.

I am attaching the sample data sheet. Any pointers to solve this problem will be appreciated.

Thank you

Jacob

Thank you so much AZ-XL. That was so quick. I am slapping myself for wasting one whole week on this without any success!!