ORIGINAL QUESTION:

A A B B B B B
C C C
M S M N O R S
M P S
1 1 4 5 6 7 10 11
12 13 14
2 15 18 19 20 21 24 25
26 27 28
3 29 32 33 34 35 38 39
40 41 42
4 43 46 47 48 49 52 53
54 55 56
5 57 60 61 62 63 66 67
68 69 70

Lets say i have data like above (where there is a main header -A,B,
or
C) and then a sub header, (M/S/N/O/R/P). Then there is a date in the
first column (indicated here by the numbers 1-5 down the left side).


A B C
4
5
6


I then have a table like the one above. What I'm trying to do is get
the "S" value returned for each of the A, B, C main headers on the
given dates (4,5 and 6 in this example). What i've tried to do is
write a nested matching/vlookup function, but I have failed thus far.
IE, write a vlookup function that looks up the date. Have the column
it uses be a match looking for S in the sub header row where the
lookup array is returned using a match of the main header row and
then
adding a few columns. Any ideas on how I can accomplish this?


And I hope I explained everything well enough for everyone to
understand =)


ORIGINAL SOLUTION:

Source data as posted assumed within A1:K7

In M2: S
In N2 across: A, B, C ...
In M4 down: 4, 5, 3 ...

Array-enter in N3, copy across/fill down to P3:
=INDEX(OFFSET($A$3:$A$7,,MATCH(1,($B$1:$K$1=N$2)*( $B$2:$K$2=$M$2),
0)),MATCH*($M3,$A$3:$A$7,0))



NEW PROBLEM:

I have entered the following as array entered based on what I was
shown in the above solution. It works fine as long as i have the
Targets.xls file open. When I close it, it gives me the #VALUE
error. It also slightly changes the formula (shows full path). I
have a temporary workaround by openeing the Targets.xls file, but I
would like to avoid having to do this. Any ideas?

With file open:
=INDEX(OFFSET([Targets.xls]HUTP!$H$8:$H$187,,MATCH(1,
([Targets.xls]HUTP!$I$6:$IV$6="WEEK ADJ")*([Targets.xls]HUTP!$I$7:$IV
$7="HU MELT"),0)),MATCH($A3,[Targets.xls]HUTP!$H$8:$H$187,0))

With file closed:
=INDEX(OFFSET('S:\mfg\CFM\Reports\TOC_Reports\[Targets.xls]HUTP'!$H
$8:$H$187,,MATCH(1,('S:\mfg\CFM\Reports\TOC_Report s\[Targets.xls]HUTP'!
$I$6:$IV$6="WEEK ADJ")*('S:\mfg\CFM\Reports\TOC_Reports\
[Targets.xls]HUTP'!$I$7:$IV$7="HU MELT"),0)),MATCH($A3,'S:\mfg\CFM
\Reports\TOC_Reports\[Targets.xls]HUTP'!$H$8:$H$187,0))

Thanks.