table lookup function

  • Thread starter Thread starter Stewy p
  • Start date Start date
S

Stewy p

I need help in looking up the a table that is sorted by product and by
dept
and date. (In my actual file is around 5,000 rows/records)

eg.
product dept date
111 1111 feb-01-03
111 1111 jan-01-03
123 1111 feb-28-03
123 1234 feb-14-03
123 1234 jan-11-03
124 1111 feb-01-03
124 1234 jan-01-03


Once I type in a department (in cell a2), I want it to come up with the
products they most recently acquired. I managed to get cell b2 and c2
to come up. but cell b3 and c3 I can't do it.

e.g.

dept product date
1234 123 feb-14-03
1234 124 jan-01-03
 
Stewy p said:
I need help in looking up the a table that is sorted by product and by
dept and date. (In my actual file is around 5,000 rows/records) ....
Once I type in a department (in cell a2), I want it to come up with the
products they most recently acquired. I managed to get cell b2 and c2
to come up. but cell b3 and c3 I can't do it.

e.g.

dept product date
1234 123 feb-14-03
1234 124 jan-01-03

If your source data were in a range named Data (indluding the top row of
field names), you could use these formulas.

A3: [array formula]
=IF(SUM((INDEX(Data,0,2)=$A$2)/
(MMULT(--(INDEX(Data,0,1)&"|"&INDEX(Data,0,2)
=TRANSPOSE(INDEX(Data,0,1)&"|"&INDEX(Data,0,2))),
--(INDEX(Data said:
ROW()-ROW($A$2),$A$2,"")

B3: [array formula]
=IF(A3<>"",INDEX(Data,MATCH(1,(INDEX(Data,0,2)=$A$2)
-(COUNTIF(B$2:B2,INDEX(Data,0,1))>0),0),1),"")

C3: [array formula]
=IF(A3<>"",MAX(IF((INDEX(Data,0,2)=$A$2)*(INDEX(Data,0,1)=B3),
INDEX(Data,0,3))),"")

Select A3:C3 and fill down as far as needed. Someone else may be able to
shorten the A3 formula. The other two are near optimal.
 
Back
Top