I INDEX MATCH ??

  • Thread starter Thread starter Mkuria
  • Start date Start date
M

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.
 
.. 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
Click to expand...

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