lookup problem in excel

[RichardO]

Hi all:

I don't know if they following is possible or not in excel, but pleas
take a look:

I have some columns of data. Column A & B have the name of account an
due date respectively. I would like to put in column H a formula tha
will lookup the account name and due date in row 6 (A6 & B6) in th
whole column A & B and excel should return the value in column D if i
finds a row with the same name but with due date 1 month prior to wha
is in B6. Is is possible to do this in excel?

for example
Acc Name Due Date outstanding penalty date paid
(on previous mth)
Samclub 02/23/2004 $1000 $50 02/20/2004
Zellers 02/03/2004 $1000 $30 02/01/2004
Winners 02/20/2004 $5000 $0 02/23/2004
Dry 02/12/2004 $-600 $55 02/12/2004
Winners 03/20/2004 $-1000 $20 02/12/2004
Dry 03/12/2004 $0 $0 02/12/2004


so say in row 6, I have Acct name: Dry and Due Date of 03/12/2004.
want to put a formula in h6, such that it will find row 4, with th
same account name, but the due date is 1 month prior, 02/12/2004,
would like excel to return the value in column D of row 4, i.e d4
being $55.

If excel doesn't find a row value with a month prior, it should retur
in H6, the value in C6.

I really need your help, please help.

Thanks.

Richard

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(D1100,MATCH(1,(A6=A1:A100)*(DATE(YEAR(B6),MONTH(B6)-1,DAY(B6))
=B1:B100),0))

Note: This may not work for end of month dates as for example
B6=31-Mar-2004 and B6-1month = 2-Mar-2004
If you have to consider this also you have to replace the formula with
=INDEX(D1100,MATCH(1,(A6=A1:A100)*(DATE(YEAR(B6),MONTH(B6)-1,MIN(DAY(
B6),DAY(DATE(YEAR(B6),MONTH(B6),0))))=B1:B100),0))

 

[Ron Rosenfeld]

Hi all:

I don't know if they following is possible or not in excel, but please
take a look:

I have some columns of data. Column A & B have the name of account and
due date respectively. I would like to put in column H a formula that
will lookup the account name and due date in row 6 (A6 & B6) in the
whole column A & B and excel should return the value in column D if it
finds a row with the same name but with due date 1 month prior to what
is in B6. Is is possible to do this in excel?

for example
Acc Name Due Date outstanding penalty date paid
(on previous mth)
Samclub 02/23/2004 $1000 $50 02/20/2004
Zellers 02/03/2004 $1000 $30 02/01/2004
Winners 02/20/2004 $5000 $0 02/23/2004
Dry 02/12/2004 $-600 $55 02/12/2004
Winners 03/20/2004 $-1000 $20 02/12/2004
Dry 03/12/2004 $0 $0 02/12/2004


so say in row 6, I have Acct name: Dry and Due Date of 03/12/2004. I
want to put a formula in h6, such that it will find row 4, with the
same account name, but the due date is 1 month prior, 02/12/2004, I
would like excel to return the value in column D of row 4, i.e d4,
being $55.

If excel doesn't find a row value with a month prior, it should return
in H6, the value in C6.

You've possibly left out some specifications, but:


=IF(COUNTIF($A$2:A6,A6)=1,C6,SUMPRODUCT((AcctName=A6)*(DueDate=EDATE(B6,-1))*Penalty))

will do what you state. Name your columns according to the obvious in the
formula above. As written, the formula can be dragged up (or down) and the
cell references should adjust appropriately.

If the EDATE function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and
then click OK.
If necessary, follow the instructions in the setup program.


--ron