Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I INDEX MATCH ??
TABLE A
LOC Date SVC 100 11/1/93 384 100 11/1/93 476 100 11/1/93 491 100 11/1/93 647 100 11/1/93 711 100 1/1/93 885 TABLE B DATE SVC Start# SVC END# LOC SEC type form 9/1/91 1454 852351 100 27 25 5-8 9/1/91 857911 2699852 100 27 25 8-16 9/1/91 4800881 4964811 100 45 42 6 4/1/92 577 332922 100 28 32 1-4 4/1/92 335898 468922 100 28 32 4-5 4/1/92 4681038 760298 100 28 32 5-8 4/1/92 760945 938883 100 28 32 8-10 4/1/92 939708 1131800 100 28 32 10-12 4/1/92 1133852 1258391 100 28 32 12-13 I need to look up data in table 1 and if date matches table 2 and SVC falls within a given range in start and end svc in table 2 - copy SEC,TYPE and FORM.(these columns are in txt format).. I used INDEX(TABLE2,MATCH(DAte,DATE(TABLE2),0),MATCH(SVC< = START SVC,0),MATCH(SVC=END SVC#,0) but this does not work. -- mmk |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I INDEX MATCH ??
.. look up data in table 1 and if date matches table 2
and SVC falls within a given range in start and end svc in table 2 copy SEC,TYPE and FORM Here's one way, illustrated in this sample: http://freefilehosting.net/download/418ha MultiCriteria Index n Match.xls Normal ENTER in D3, copy across/fill down to populate: =IF(ISNA(MATCH(1,INDEX(($B3=$A$11:$A$19)*($C3=$B$ 11:$B$19)*($C3<=$C$11:$C$19),),0)),"",INDEX(E$11:E $19,MATCH(1,INDEX(($B3=$A$11:$A$19)*($C3=$B$11:$B $19)*($C3<=$C$11:$C$19),),0))) -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I INDEX MATCH ??
Hi,
Suppose your Table 1 starts in A1 and that you want to return the results to columns D:F (Sec, Type, Form) and suppose your Table 2 goes from A10 to G19 then in cell D2 enter the following formula to return the Sec and copy it over to E2 and down as far as needed. =SUMPRODUCT(--($B2=$A$11:$A$19),--($C2=$B$11:$B$19),--($C2<=$C$11:$C$19),E$11:E$19) If you want to use range names then Start, End, and Date would be useful (in table 2) =SUMPRODUCT(--($B2=Date),--($C2=Start),--($C2<=End),E$11:E$19) Without looking at your formula, I notice that no data in Table 1 would match any of the rows in Table 2? If this helps, please click the Yes button. -- Thanks, Shane Devenshire "Mkuria" wrote: TABLE A LOC Date SVC 100 11/1/93 384 100 11/1/93 476 100 11/1/93 491 100 11/1/93 647 100 11/1/93 711 100 1/1/93 885 TABLE B DATE SVC Start# SVC END# LOC SEC type form 9/1/91 1454 852351 100 27 25 5-8 9/1/91 857911 2699852 100 27 25 8-16 9/1/91 4800881 4964811 100 45 42 6 4/1/92 577 332922 100 28 32 1-4 4/1/92 335898 468922 100 28 32 4-5 4/1/92 4681038 760298 100 28 32 5-8 4/1/92 760945 938883 100 28 32 8-10 4/1/92 939708 1131800 100 28 32 10-12 4/1/92 1133852 1258391 100 28 32 12-13 I need to look up data in table 1 and if date matches table 2 and SVC falls within a given range in start and end svc in table 2 - copy SEC,TYPE and FORM.(these columns are in txt format).. I used INDEX(TABLE2,MATCH(DAte,DATE(TABLE2),0),MATCH(SVC< = START SVC,0),MATCH(SVC=END SVC#,0) but this does not work. -- mmk |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I INDEX MATCH ??
Sumproduct unfortunately fails when it come to "returning" text or mixed
data. OP had a col of text to be returned ("Form" col). That's gonna just show up as zeros as the sumproduct result. IMO, index/match would be a better generic option to use, works for all returns, numeric or otherwise. -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I INDEX MATCH ??
Thanks Max and Shane for responding -- the formula worked.
-- mmk "Mkuria" wrote: TABLE A LOC Date SVC 100 11/1/93 384 100 11/1/93 476 100 11/1/93 491 100 11/1/93 647 100 11/1/93 711 100 1/1/93 885 TABLE B DATE SVC Start# SVC END# LOC SEC type form 9/1/91 1454 852351 100 27 25 5-8 9/1/91 857911 2699852 100 27 25 8-16 9/1/91 4800881 4964811 100 45 42 6 4/1/92 577 332922 100 28 32 1-4 4/1/92 335898 468922 100 28 32 4-5 4/1/92 4681038 760298 100 28 32 5-8 4/1/92 760945 938883 100 28 32 8-10 4/1/92 939708 1131800 100 28 32 10-12 4/1/92 1133852 1258391 100 28 32 12-13 I need to look up data in table 1 and if date matches table 2 and SVC falls within a given range in start and end svc in table 2 - copy SEC,TYPE and FORM.(these columns are in txt format).. I used INDEX(TABLE2,MATCH(DAte,DATE(TABLE2),0),MATCH(SVC< = START SVC,0),MATCH(SVC=END SVC#,0) but this does not work. -- mmk |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I INDEX MATCH ??
Welcome. But I'm puzzled why you pressed the "No" button instead of "Yes" in
my response? Please correct the rating. -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- "Mkuria" wrote: Thanks Max and Shane for responding -- the formula worked. -- mmk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
index,match,match on un-sorted data | Excel Worksheet Functions |