Lookup remaining balance by month and payee

csenor

Board Regular
Joined
Apr 10, 2013
Messages
169
Office Version
  1. 365
Platform
  1. Windows
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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]CREDIT CARD 1[/TD]
[TD]CREDIT CARD 2[/TD]
[TD]COLLEGE LOAN[/TD]
[TD]CAR LOAN[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3/1/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4/1/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5/1/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
I see it working for you, however, when I plugged that into my worksheet, I got all zeros. I'll look more closely.
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Forum statistics

Threads
1,223,270
Messages
6,171,103
Members
452,379
Latest member
IainTru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top