Summary Tab - returning data from Dates

[Confused Excel]

I want to create a summary tab for an excel sheet where I return the points used every month for a client without having to manually add it.

So for example I have columns stating the date Sep 21 Oct 21 Nov 21 and the corresponding points in the cell below. I would like to be able to input a month date in the summary tab and it return the points used for that month - presumably by searching for that date column and returning the points. I tried with a vlookup but the date range would have to be 1st column which doesnt quite work for this exercise and likely something a bit more complex needed any help would be appreciated

 

[bosco_yip]

In "Summary" sheet C4, formula copied down :

=INDEX(Data!C$3:E$7,MATCH(B4,Data!A$2:A$6,0),MATCH(F$2,Data!C$1:E$1,0))

 

[Confused Excel]

Thank you for the swift response, will this formula work when I add on Dec 21 figures next month or will I need to edit it each time (which I would like to avoid obviously).

 

[bosco_yip]

Thank you for the swift response, will this formula work when I add on Dec 21 figures next month or will I need to edit it each time (which I would like to avoid obviously).

If your Data sheet data will be increased, you could extend the formula range to a longer range :

=INDEX(Data!C$3:XFD$1000,MATCH(B4,Data!A$2:A$999,0),MATCH(F$2,Data!C$1:XFD$1,0))

or,

You could use Excel Table for the auto formula growing.

 

[Confused Excel]

Amazing, I will try that out - thank you very much

 

[Confused Excel]

Hi, I have tried your formula but cant seem to get it working when I try and extrapolate it out for my sheet.

Not sure what I am doing wrong but I have copied the framework exactly, I cant quite understand the B2, B4 B6 choice in the second MATCH. I have uploaded what the format will be in this attachment, are you able to show the formula needed for that? Only interested in showing the closing balance depending on which month I choose - thanks again

 

[bosco_yip]

Hi, I have tried your formula but cant seem to get it working when I try and extrapolate it out for my sheet.

Not sure what I am doing wrong but I have copied the framework exactly, I cant quite understand the B2, B4 B6 choice in the second MATCH. I have uploaded what the format will be in this attachment, are you able to show the formula needed for that? Only interested in showing the closing balance depending on which month I choose - thanks again

In "Summary" sheet C4, formula copied down :

=INDEX(Data!B$12:X$19,MATCH(B4,Data!A$7:A$14,0),MATCH(F$2,Data!B$5:X$5,0))

 

[Confused Excel]

I noticed I wasnt using the correct client names, once changed it worked

Thank you very much!