I INDEX MATCH ??

[Mkuria]

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.

 

[Max]

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

 

[ShaneDevenshire]

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.

 

[Max]

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

 

[Mkuria]

Thanks Max and Shane for responding -- the formula worked.

 

[Max]

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