44judester;443685 Wrote:
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' ('The Code Cage - Microsoft Office Help - Microsoft
Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
NBVC's Profile: 'The Code Cage Forums - View Profile: NBVC'
(http://www.thecodecage.com/forumz/members/nbvc.html)
View this thread: 'multi-level indexing? - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=122286)



The last match looks at two conditions... first whether the 2nd row is
OT or ST... and the other conditon checks for the Month... since you
only have months on every other cell in the row.. it needs to check if
the cell in the same column as the OT, ST is filled or not.. the + sign
acts as an OR checking if cell in same column or cell in column to the
left has the correct month.

The 1 at the beginning, looks for the first position of a 1 in the
resulting array of this lookup array (which is also a conditional
array): (Sheet1!$C$2:$F$2=D2)*((Sheet1!C1:F1=C2)+(Sheet1!B 1:E1=C2))


--
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