Last Occurence of last month Transaction using Index sumproduct

[tlchan]

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

sample file attached

[mdmackillop]

On the same sheet for simplicity

[NoSparks]

The max portion of your formula is determining the sheet row number and INDEX requires the table row number.

[tlchan]

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.

[mdmackillop]

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))

[tlchan]

Thanks mdmackillop! You save my day

[mdmackillop]

Happy to help. I played around a bit more to implement range names making it more flexible and user friendly.