Not sure how that last MATCH sequence works, but it does! Thanks!

"NBVC" wrote:


Say your table is in Sheet1!A1:F17, whe

A3:A17 list your Depts
B3:B17 list your Shifts
C1 shows Oct and E1 shows Sep
C2:F2 lists ST or OT, respectively for the months


then in Sheet2:

A2: Dept (e.g 444)
B2: Shiif (e.g. 2nd)
C2: Month (e.g Sep)
D2: ST or OT (e.g. OT)

then formula to extract intersect:


Code:
--------------------
=INDEX(Sheet1!$C$3:$F$17,MATCH(A2&B2,Sheet1!$A$3:$ A$17&Sheet1!$B$3:$B$17,0),MATCH(1,(Sheet1!$C$2:$F$ 2=D2)*((Sheet1!C1:F1=C2)+(Sheet1!B1:E1=C2)),0))
--------------------


this formula is an array formula and must be confirmed with
CTRL+SHIFT+ENTER not just ENTER.


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122286