lookup lastmonth

[Mike]

ColA ColB
1/2/2003 ABC
1/3/2003 EEE
1/4/2003 EEE
2/5/2003 EEE
2/6/2003 ABC
2/7/2003 EEE
2/8/2003 EEE
3/10/2003 EEE
3/11/2003 EEE
3/12/2003 ABC
3/13/2003 EEE
3/14/2003 EEE
4/25/2003 ABC
4/26/2003 EEE
4/27/2003 EEE
4/28/2003 EEE
5/2/2003 ABC

If today is 5/2/2003, how the formulas or macros can be
searched the date of ABC in last month
I want the result will be

ColA ColB
4/25/2003 ABC
5/2/2003 ABC

Thanks,

 

[Leo Heuser]

Mike

Here's a formula approach to your problem, assuming that
only one instance of ABC exists for each month (which
your example implies).

Assumptions

Data in A2:A18

In e.g. C14 enter the formula =TODAY()
In D14 enter ABC
In D15 enter =D14
In C15 enter the formula

=SUMPRODUCT((MONTH(A2:A18)=MONTH(C14)-1)*(B2:B18=D14)*A2:A18)


--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only, please.