Return information in one cell based on search in other sheets

[Mike]

I need a formula to fill in names in sheet 3 into the time slots that
correspond with thier assignments in sheets 1 and 2. For instance, the
formula in cell d6 would search sheets 1 and 2 until it find "SA 60 4P-12M"
until it found the information found in cell d8 in sheet 2. Notice that in d8
"W/T" is in between "SA 60" and "4P-12M" but not allows.

SHEET 1:
SHIFT NAMES SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY
JONES WP WP DO DO WP WP WP
ALLEN W SA 60 8A-4P W W W DO DO
THOMAS SA 60 W/T 8A-4P SA 61/1 8A - 8P DO DO/SA 60 8A-4P W W W
SMITH SA 60 4P-12M W WP WP SA 60 4P-12M SA 60 8A-4P SA 61/1 8A - 8P
DOE SA 61/1 8A - 8P SA 60 4P-12M W W SA 60 W/T 8A-4P SA 60 4P-12M DO
PETTERSON WR W SA 60 8A-4P W SA 60 8A-4P DO SA 60 4P-12M
WASHINGTON WJ DO DO/SA 60 4P-12M W SA 61/1 8A - 8P SA 61/1 8A - 8P W
HAMILTON DO DO SA 61/1 8A - 8P SA 60 4P-12M SA 61/1 8A - 8P W SA 60 8A-4P
FRANKLIN DO WR WR SA 61/1 8A - 8P W DO A/L


SHEET 2:
SHIFT NAMES SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY
JEFFERSON WP WP DO DO WP WP WP
BULLER W SA 60 8A-4P W W W DO DO
MILLER SA 60 8A-4P SA 61/1 W/T 8A - 8P DO SA 60 8A-4P W W W
ROMEO DO/SA 60 4P-12M W WP WP SA 60 4P-12M SA 60 8A-4P SA 61/1 W/T 8A - 8P
HARLAN SA 61/1 8A - 8P SA 60 W/T 4P-12M W W SA 60 8A-4P SA 60 4P-12M DO
PERRY WR W SA 60 8A-4P W SA 60 8A-4P DO SA 60 4P-12M
BEARD WJ DO SA 60 W/T 4P-12M W SA 61/1 8A - 8P SA 61/1 8A - 8P W
GOMEZ DO DO SA 61/1 8A - 8P SA 60 4P-12M SA 61/1 8A - 8P W SA 60 8A-4P
NEAL DO WR WR SA 61/1 8A - 8P W DO A/L


SHEET 3:

ASSINGMENT SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY
SA 60 8A - 4P 8A - 4P 8A - 4P 8A - 4P 8A - 4P 8A - 4P 8A - 4P


4P- 12M 4P- 12M 4P- 12M 4P- 12M 4P- 12M 4P- 12M 4P- 12M
BEARD

SA 61/1 8A - 8P 8A - 8P 8A - 8P 8A - 8P 8A - 8P 8A - 8P 8A - 8P

 

[Mike]

I also added another sheet, #5 and in A:1 wrote SA 60 8a-4p.
So far, I have tried =VLOOKUP(Sheet5!A1,'Sheet1''Sheet2'!B1:H10,1,TRUE)