LOOKUP on mutiple columns

[trident22]

Hi,

In Excel 2002 what formula(s) can I use to return a figure from the third column based on both a date in the 1st column and a code in the 2nd column.


In SQL it would be approx. SELECT quant FROM xxx WHERE (vdate=dateVar And code=codeVar).

Mant thanks,
Al


06/03/2004 0
06/03/2004 MG 11
06/03/2004 YG 40
07/03/2004 0
07/03/2004 MG 5
07/03/2004 YG 14
08/03/2004 0
08/03/2004 MG 19
08/03/2004 YG 69
09/03/2004 0
09/03/2004 MG 12
09/03/2004 YG 61
10/03/2004 0
10/03/2004 MG 7
10/03/2004 YG 63
11/03/2004 0
11/03/2004 MG 3
11/03/2004 YG 51
12/03/2004 0
12/03/2004 MG 4
12/03/2004 YG 43

 

[KenWright]

=SUMPRODUCT((RngA=x)*(RngB=y)*(RngC))

Ranges must be of equal length. With dates you need to ensure that function recognises what you put in as a date,

=SUMPRODUCT((RngA=DATEVALUE("19/3/2004"))*(RngB="MG")*(RngC))

or put the criteria in other cells and reference the cells, eg:-

=SUMPRODUCT((RngA=H1)*(RngB=H2)*(RngC))

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------