Vlookup for calendar date

eazyyexcel

New Member
Joined
Mar 1, 2021
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
Hi there,

I have attached 2 pictures in my thread.

1. Vlookup
Basically we receive payments on a monthly basis from our clients, example for May it can be anytime from 01/05/2021 to 31/05/2021.

2. Vlookup2
In another tab, I list down the summary based on a month. Example what is the total payment received, total interest received, total principal and etc.

How do I create a formula to vlook up the entire month date from 01/05/2021 to 31/05/2021 and for the data to be displayed in the summary tab.

Please help.

Thank you.
 

Attachments

  • vlookup.png
    vlookup.png
    4.9 KB · Views: 13
  • vlookup2.png
    vlookup2.png
    6.5 KB · Views: 14

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Hi EazyExcel,

Assuming you're driven by the Date column on the schedule and that the Summary is for a specific year then SUMIFS would work.

EazyExcel.xlsx
ABCDEFG
1
2MonthDateCodeMadeInterestPrincipalBalance
3May-215/5/2021ABC4002002201780
4Jun-21
5Jul-21
Schedule


EazyExcel.xlsx
ABCD
1MonthsPaymentInterestPrincipal
2Apr-21000
3May-21400200220
4Jun-21000
5Jul-21000
6Aug-21000
Summary
Cell Formulas
RangeFormula
B2:D6B2=SUMIFS(Schedule!D:D,Schedule!$B:$B,">="&Summary!$A2,Schedule!$B:$B,"<="&EOMONTH(Summary!$A2,0))
A3:A6A3=EOMONTH(A2,0)+1
 
Upvote 0
Hello Toadstool

Thank you for the quick reply. Hold on, I am new to this forum and I'm not sure what to do with what you have just explained.

Under the "cell formulas" tab, you had explained from b2:d6, i need to insert the formula? but the problem is I am keying in data into that cell. Unless you're saying to insert the formula in my summary tab? is that what I am supposed to do?

To be honest I am kind of lost. I believe what you have given, I am able to display on my worksheet to understand how it works, but the problem is I tried opening an empty excel sheet and I clicked the copy button on your tables (beside A cell), and I pasted it to an empty excell sheet but all the data is coming out as #value. Not sure how it works. I tried copying your entire formula "=SUMIFS(Schedule!D:D,Schedule!$B:$B,">="&Summary!$A2,Schedule!$B:$B,"<="&EOMONTH(Summary!$A2,0))" and I pasted it on my summary tab, and immediately a window pops up where I can select a file from my laptop, not sure what is happening.

I am sorry but it is a lil overwhelming for me, if you could explain in layman, I'd really appreciate it.

Thank you very much for your time
 
Upvote 0
Hi EazyExcel,

You can see I pasted two sheets in XL2BB. The first is called Schedule and it's where you type in your data.
The second sheet is called Summary and it's where the formula resides.
If you don't paste into the named sheets from XL2BB then the formula can't find the sheets and gives #value.
 
Upvote 0
Wow I just tried it and it works! Thank you very much. The problem now I have is, how do I get it to work on my original file? Example I have attached a screenshot of my original file here. I have copied your formula and pasted it onto cell B3 of the summary sheet. Then as you mentioned, I need to change the named sheets hence I changed the name sheets "Schedule" to "SJJ Clients" and the "Summary" sheet remains the same. How come the error is still popping up? The error is #NAME?

I have also taken a screenshot of my "SJJ Clients" and "Summary" sheet. I believe the error is because the rows and columns are not identified correctly? could that be so? Your help would be appreciated. I definitely need more tutorials on Excel!!
 

Attachments

  • sjj clients.jpg
    sjj clients.jpg
    200.4 KB · Views: 7
  • summary sheet.png
    summary sheet.png
    51.8 KB · Views: 8
Upvote 0
I see the data starts at row 114 on SJJ Clients so be aware that as we're using all of columns B, D, E and F in the formulae that any rows matching the criteria above, or below, rows 114 will also be included in the Summary.

EazyExcel2.xlsx
ABCDEFG
112Payment Schedule
113MonthDateCodeMadeInterestPrincipalBalance
114May-215/5/2021ABC4002002201780
115Jun-21
116Jul-21
SJJ Clients


When you use a sheet name in a formula like Schedule then you just type the name, but if the name has a space in it then Excel requires you wrap it in single quotes.

EazyExcel2.xlsx
ABCD
1SJJ Accounts
2MonthsPaymentInterestPrincipal
3Apr-21000
4May-21400200220
5Jun-21000
Summary
Cell Formulas
RangeFormula
B3:D5B3=SUMIFS('SJJ Clients'!D:D,'SJJ Clients'!$B:$B,">="&Summary!$A3,'SJJ Clients'!$B:$B,"<="&EOMONTH(Summary!$A3,0))
A4:A5A4=EOMONTH(A3,0)+1
 
Upvote 0
Solution
Hello Toadstool,

Unfortunately the formula is still not working on my sheet. I have attached 2 screenshots (1st one is B3 cell formula and 2nd one is A4 cell formula).

I have copied the exact same formula you created and pasted it on cell B3 but the data is showing 0. You are correct, the data starts on row 114 on SJJ clients, so it should reflect...I dont know what is wrong.
 

Attachments

  • summary sheet b3 cell formula.png
    summary sheet b3 cell formula.png
    42.5 KB · Views: 5
  • summary sheet a4 cell formula.png
    summary sheet a4 cell formula.png
    40.7 KB · Views: 5
Upvote 0
I suspect that A3 showing "April" does not have the actual date. Put the 1st April 2021 in you region format and it should work.
 
Upvote 0
Hi again. You asked me to explain what the formula does so here I go.

Your original data sheet called "SJJ Clients" looks like this:
EazyExcel2.xlsx
ABCDEFG
112Payment Schedule
113MonthDateCodeMadeInterestPrincipalBalance
114May-215/5/2021ABC4002002201780
115Jun-21
116Jul-21
SJJ Clients


The formula resides in a table on the "Summary" sheet
EazyExcel2.xlsx
ABCD
1SJJ Accounts
2MonthsPaymentInterestPrincipal
3Apr-21000
4May-21400200220
5Jun-21000
Summary
Cell Formulas
RangeFormula
B3:D5B3=SUMIFS('SJJ Clients'!D:D,'SJJ Clients'!$B:$B,">="&Summary!$A3,'SJJ Clients'!$B:$B,"<="&EOMONTH(Summary!$A3,0))
A4:A5A4=EOMONTH(A3,0)+1



Cell A3 has the 1st of the starting month date, in this case 4/1/2021 or 1st April 2021 as I have a US PC.

Cell A4 has =EOMONTH(A3,0)+1 and EOMONTH gives the last day of the provided date with a number of months added. I'm taking end of 4/1/2021 which is 4/30/2021 and adding zero months so it remains 4/30/2021 then I'm adding 1 to it because dates are held as integer values since 1 Jan 1900 so adding one day gets me 5/1/2021, the first day for the May summary. I do the same for A5 to get 6/1/2021.

Because you are using a Table then formulae will automatically extend so if you select D5 and press Tab it will add another row and populate the formulae so it's ready for July.

The SUMIFS formula for the only month with data, row 4, looks like this:
Excel Formula:
=SUMIFS('SJJ Clients'!D:D,'SJJ Clients'!$B:$B,">="&Summary!$A4,'SJJ Clients'!$B:$B,"<="&EOMONTH(Summary!$A4,0))

SUMIFS has a parameter of the range to sum followed by pairs of criteria range and criteria.
The range we want to sum is on the SJJ Clients sheet in column D so we enter that SUMIFS('SJJ Clients'!D:D because column D contains the value to be summed.
Q. Why SUMIFS('SJJ Clients'!D:D and not SUMIFS('SJJ Clients'!$D:$D ?
A. Because we use relative addressing of column D as the Payment, Interest and Principal are next to each other in the same sequence so when I copy this formula to the right it will change D to E then F so we pick up the correct SJJ Clients columns.

Next are the criteria range and criteria. The criteria range is 'SJJ Clients'!$B:$B,">="&Summary!$A4 so it will check the Date of the SJJ Clients transaction and make sure it is greater than or equal to the summary date in Summary column A, in this case 5/1/2021.
Q. Why do some addresses have a $ and some not?
A. I want to make it so the formula can be copied across and down for the other values and months so I know I always want to check the date column in SJJ Clients so I make that address absolute with 'SJJ Clients'!$B:$B but the Summary month is also fixed in column A so I use $A but I want it to pick up other months so the row is relative and will change when copied down, hence Summary!$A4.
NOTE: One quirk of Excel is that some functions require the operators be wrapped in double quotes and the value appended with ampersand "&". e.g. if you want to check if X1 is not equal to Z1 then you use =IF(X1<>Z1,true,false) but if you want to use COUNTIF, SUMIF, etc then it requires COUNTIF(X1,"<>"&Z1)

Now comes the second criteria because the date from SJJ Clients can be any day of the month. We've already checked that the SJJ Clients dates are equal or greater than the 1st May 2021 so now we check the date is less than or equal to the last day of May,
'SJJ Clients'!$B:$B,"<="&EOMONTH(Summary!$A4,0)
I explained EOMONTH above so here we check all SJJ Client dates for being less than or equal to end of month A4 plus zero months, so it checks for date <= 31 May 2021.


I'm sure that's more of an explanation than you wanted so I'll stop now ;)
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

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