Double Match - version 2

[John]

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_Reports\[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.

 

[Max]

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 Targets.xls open, try replacing the above with this pure INDEX version
of the earlier suggestion:
=INDEX([Targets.xls]HUTP!$I$8:$IV$187,MATCH($M3,[Targets.xls]HUTP!$H$8:$H$187,0),MATCH(1,([Targets.xls]HUTP!$I$6:$IV$6="WEEK
ADJ")*([Targets.xls]HUTP!$I$7:$IV$7="HU MELT"),0))
(The above is still to be array-entered, as before)

From my tests here, the pure INDEX version seems to work ok, even when
Targets.xls is closed

 

[Harlan Grove]

With Targets.xls open, try replacing the above with this pure INDEX version
of the earlier suggestion:

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

....

Better (as in shorter and fewer function calls) to use a hybrid
VLOOKUP/MATCH approach.

=VLOOKUP($M3,[Targets.xls]HUTP!$H$8:$IV$187,
MATCH(1,([Targets.xls]HUTP!$I$6:$IV$6="WEEK ADJ")*([Targets.xls]HUTP!$I
$7:$IV$7="HU MELT"),0),0)

 

[Max]

Better (as in shorter and fewer function calls) to use a hybrid
VLOOKUP/MATCH approach.
=VLOOKUP($M3,[Targets.xls]HUTP!$H$8:$IV$187,
MATCH(1,([Targets.xls]HUTP!$I$6:$IV$6="WEEK ADJ")*([Targets.xls]HUTP!$I
$7:$IV$7="HU MELT"),0),0)

Good to see you around, Harlan
Believe that a "+1" adjustment should be made to the col index param:
=VLOOKUP($M3,[Targets.xls]HUTP!$H$8:$IV$187,MATCH(1,([Targets.xls]HUTP!$I$6:$IV$6="WEEK
ADJ")*([Targets.xls]HUTP!$I$7:$IV$7="HU MELT"),0)+1,0)

 

[John]

Appears to have worked. Thanks again for everyones help. Any
suggestions on a good place to understand the basics of array-entered
functions? I can use vlookup and match in their "normal" forms, but
I'm at a loss as to exactly how/what the above functions are doing (I
can edit them to account for where my data exactly is, etc, but I
couldn't create one from scratch).

 

[Max]

Any suggestions on a good place to understand

the basics of array-entered functions?

Let's take the simpler* expression below I gave you in your earlier post:
MATCH(1,($B$1:$K$1=N$2)*($B$2:$K$2=$M$2),0)
*w/o the distractions of sheetnames, filenames, etc distorting the core
picture

Multiplying the 2 equal sized conditional ranges, viz this part:
($B$1:$K$1=N$2)*($B$2:$K$2=$M$2)
produces an array of zeros/a single "1" eg: {0,1,0,0,0,0,0,0,0,0} depending
on whether the dual conditions are simultaneously satisfied ("1") or not
(zeros)

Then, matching for a "1" in that resulting array ie:
MATCH(1,{0,1,0,0,0,0,0,0,0,0},0)
will give us the position of the single "1" within the array (the position
is a number). The position returned (2 in this case) can then be used for
whatever purpose, for example in the earlier vlookup, it is used as the col
index param (with the arithmetic adjustment "+1")

For more examples in Excel newgroup archives, try googling eg:
array formula explanation group:*excel*