Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup lastmonth
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup lastmonth
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. "Mike" skrev i en meddelelse ... 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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions |