# Lookup remaining balance by month and payee

#### csenor

##### Board Regular
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>

Last edited:

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Hi, not sure to understand your query "last value of that month", however shouldn't you add a column with payee name?
Your data is not optimal for pivot. better to minimise number of columns such as:
 name date type amount A 1/1/14 COLLEGE LOAN 627 B 1/2/14 CREDIT CARD 1 132 A 1/3/14 CREDIT CARD 2 120 E 1/4/14 COLLEGE LOAN 549 B 1/1/14 CAR LOAN 173 C 1/2/14 CREDIT CARD 2 230 D 1/3/14 CAR LOAN 189 A 1/4/14 CREDIT CARD 1 895

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

My debt payment table is set up like your example. What I mean by last value of that month is I want to locate the last date a payment was made by each payee made and return the remaining balance. I want to be able to create a chart to show the remaining balances by month. There may be times when multiple payments are made in a month. I need to find the last one and see the remaining balance.

I've also tried to do this using the following formula. Column 7 is the remaining balance column in my debt payment table. This also does not recognize the Payee. It only recognizes the date and returns the remaining balance.

=INDEX(Table1,MATCH(\$a2&b\$1,Date&Payee,1),7)

given in A1 raw as follows:

 name date type amount A 1/1/14 COLLEGE LOAN 627 B 1/2/14 CREDIT CARD 1 132 A 1/3/14 CREDIT CARD 2 120 E 1/4/14 COLLEGE LOAN 549 B 1/1/14 CAR LOAN 173 C 1/2/14 CREDIT CARD 2 230 D 1/3/14 CAR LOAN 189 A 1/4/14 CREDIT CARD 1 895

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

results in A15 as follows:
 A 1/4/14 CREDIT CARD 1 895 B 1/2/14 CREDIT CARD 1 132 C 1/2/14 CREDIT CARD 2 230 D 1/3/14 CAR LOAN 189 E 1/4/14 COLLEGE LOAN 549

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Formula in B15 is =MAX(IF(\$A\$2:\$A\$9=A15,\$B\$2:\$B\$9)) Ctrl + Shift + Enter not just enter on a pC or Command + Return on a MAC. Copied down till needed.
Formula in C15 is =IFERROR(INDEX(C\$2:C\$9,MATCH(1,(\$A15=\$A\$2:\$A\$9)*(\$B15=\$B\$2:\$B\$9),0)),"") Ctrl + Shift + Enter not just enter on a pC or Command + Return on a MAC.
Copied to the right and down till needed.

Payment Table:
 A B C D E 1 Date Payee Payment Current Balance Remaining Balance 2 3/3/14 Credit Card 50 1500 1450 3 3/15/14 Car Loan 250 12000 11750 4 3/18/14 College Loan 75 5000 4925 5 3/20/14 Credit Card 75 1450 1375 6 4/2/14 Credit Card 50 1375 1325 7 4/15/14 Car Loan 250 11750 11500 8 4/25/14 College Loan 75 4925 4850 9 4/28/14 Credit Card 100 1500 1400

<tbody>
</tbody>

Formula Table to find remaining balance at the end of each month

 A B D E 1 CREDIT CARD COLLEGE LOAN CAR LOAN 2 3/1/14 1375 4925 11750 3 4/1/14 1400 4850 11500 4 5/1/14

<tbody>
</tbody>

Given your raw data in A1.
And given in A16:
 CREDIT CARD COLLEGE LOAN CAR LOAN 3/1/14 1375 4925 11750 4/1/14 1400 4850 11500 5/1/14

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Formula in B17 is =IFERROR(INDEX(\$E\$2:\$E\$9,SUMPRODUCT(MAX((MONTH(\$A\$2:\$A\$9)=MONTH(\$A17))*(\$B\$2:\$B\$9=B\$16)*(ROW(\$A\$2:\$A\$9))))-1,0),"") Ctrl + Shift + Enter not just Enter on a pC or Command + Return on a MAC.

Sheet1, A:E, houses the data ('Payment Table').

A2:A9 is named as Date via Name Box, B2:B9 as Payee, and E2:E9 as RBalance.

Sheet2, A:E, houses the processing ('Formula Table')

Sheet2, B2, copied across and down:

=IFERROR(LOOKUP(9.99999999999999E+307,1/((Date-DAY(Date)+1=\$A2)*(Payee=B\$1)),RBalance),"")

Thanks Aladin. I fine tuned your formula and I got it to work. This is what I did:

=IFERROR(LOOKUP(200,1/((Date>=\$A2)*(Date<=EOMONTH(\$A2,0))*(Payee=B\$1)),Remaining_Balance),"")

Thanks for answering my post Aladin and Cryilbrd.

Thanks Aladin. I fine tuned your formula and I got it to work. This is what I did:

=IFERROR(LOOKUP(200,1/((Date>=\$A2)*(Date<=EOMONTH(\$A2,0))*(Payee=B\$1)),Remaining_Balance),"")

Thanks for answering my post Aladin and Cryilbrd.

Fine-tuned?

[a] 200 in lieu of 9.99999999999999E+307? See http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html

((Date>=\$A2)*(Date<=EOMONTH(\$A2,0))

is exactly the same thing as:

(Date-DAY(Date)+1=\$A2)

and less efficient.

[c] See the workbook that implements the suggestion as I made it:
https://dl.dropboxusercontent.com/u/65698317/csenor%20Lookup%20remaining%20balance%20by%20month%20and%20payee.xlsx

Replies
1
Views
183
Replies
6
Views
188
Replies
1
Views
379
Replies
0
Views
174
Replies
1
Views
848

Threads
1,221,521
Messages
6,160,303
Members
451,637
Latest member
hvp2262

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

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