Can't quite see how C1:Z1 on worksheet 2 which is a 24 cell range, can
contain the same values as A3:A20 on worksheet 1 which is an 18 cell range,
unless of course you have duplicates, however:-

On worksheet 1 in cell B3 put the following formula and copy across to
B3:H20

=IF(INDEX(Sheet2!$A$1:$T$366,MATCH(Sheet1!B$2,Shee t2!$A$1:$A$366,0),MATCH(Sheet1!$A3,Sheet2!$A$1:$T$ 1,0))="",INDEX(Sheet2!$A$1:$T$366,MATCH(Sheet1!B$2 ,Sheet2!$A$1:$A$366,0),2),INDEX(Sheet2!$A$1:$T$366 ,MATCH(Sheet1!B$2,Sheet2!$A$1:$A$366,0),MATCH(Shee t1!$A3,Sheet2!$A$1:$T$1,0)))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------




"Caveman" wrote in
message ...

I have 2 worksheets,
Worksheet1 A3:A20 are names, B1:H1 are days of the week (SUN-SAT),
B2:H2 are days of the week to match the calender days
(2/1/2005-8/1/2005),when you change the sun date it changes the whole
weeks date.
Worksheet2 A2:A366 dates of the year (1/1/2005-31/12/2005), the next
column is based on a 4on/2off works rota system, B2:B366
on/on/on/on/off/off/on/on/on/on/off/off & so on. C1:Z1 have the same
names as sheet1 column A.

I need a formula that looks at a name & date on worksheet1, Looks at
worksheet2 for the same name and date, (J Bloggs (h1) on the 6/1/2005
(A7)) then return the data from the intersection of the grid ref, if
empty return the data from B7.


--
Caveman
------------------------------------------------------------------------
Caveman's Profile:
http://www.excelforum.com/member.php...o&userid=27546
View this thread: http://www.excelforum.com/showthread...hreadid=480429