Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Double Match - version 2
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Double Match - version 2
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Double Match - version 2
"Max" wrote...
.... 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) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Double Match - version 2
"Harlan Grove" wrote
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) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Double Match - version 2
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). |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Double Match - version 2
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* -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "John" wrote in message ... 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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Double Match | Excel Worksheet Functions | |||
Double Vertical Index & Match | Excel Worksheet Functions | |||
Double Match formula... | Excel Worksheet Functions | |||
double match formula | Excel Worksheet Functions | |||
Double and Multiple Lookup Using the MATCH Function | Excel Worksheet Functions |