Lookup remaining balance by month and payee

[csenor]

Hello. I have a table that keeps track of my debt payments. 3 of the columns are: Date, Payee, and Remaining Balance. I want to create another formula table or pivot table to see the Remaining Balance of each Payee by Month. I thought the following formula would work, but it's not recognizing the payee. It is only looking at the last date in the month and returning the remaining balance.

I tried to do this with a pivot table, but it doesn't look like it will find the last value of that month. It only lets you show the value as the min or max, which necessarily might not work if the credit card ends up going up by making a big purchase and making just the minimum monthly payment.

Formula: =LOOKUP(EOMONTH($a2,0)&B$1,Date&Payee,Remaining_Balance)

Formula Table:

	A	B	C	D	E
1		CREDIT CARD 1	CREDIT CARD 2	COLLEGE LOAN	CAR LOAN
2	3/1/14
3	4/1/14
4	5/1/14

<tbody>
</tbody>

 

[csenor]

I see it working for you, however, when I plugged that into my worksheet, I got all zeros. I'll look more closely.