Last Occurence of last month Transaction using Index sumproduct
Hi,
I try to obtain the last transaction balance of a selected currency in sheet2 from a daily transaction database in sheet1 using combination of INDEX SUMPRODUCTS and MAX but is not working.
Date in sheet2 B2 for reporting month (March 17) and I require to obtain last occurence of the respective currency in previous month (Feb 17) from sheet 1
I want to locate cell value in column F for a selected currency in last transaction of previous month. eg if the month in K8 is March then the last txn of the currency in Feb per highlight in green and yellow cell.
Apologies, totally misunderstood.
Give this a try
Array formula =INDEX($F$2:$F$12,MATCH((MAX(IF(($A$2:$A$12=$J3)*($B$2:$B$12<=EOMONTH(K$1,-1)),$B$2:$B$12))),$B$2:$B$12))
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.