Question description:
This user has given permission to use the problem statement for this
blog.
I have a column of consecutive months and want to create a row of years which will lookup the column for a specific start date and add sum that month + the next consecutive 11 to create a fiscal year total
Solved by M. Q. in 47 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
17/05/2018 - 01:38
HI
Excelchat Expert
17/05/2018 - 01:38
Welcome to Got IT Pro.
Excelchat Expert
17/05/2018 - 01:39
Please share the sample in preview
User
17/05/2018 - 01:39
thanks
Excelchat Expert
17/05/2018 - 01:40
Ok. I am reviewing the data.
Excelchat Expert
17/05/2018 - 01:40
Can you please let me know the expected output?
User
17/05/2018 - 01:40
I want cell M4 to lookup the month and sum that plus the next 11 months
User
17/05/2018 - 01:41
so cell M4 should look in column A to find "1", then add cells A3:A14
Excelchat Expert
17/05/2018 - 01:42
M4?
Excelchat Expert
17/05/2018 - 01:42
or Mm3?
User
17/05/2018 - 01:42
M4
User
17/05/2018 - 01:43
Im sorry it should look in column A to sum the corresponding values in column H
Excelchat Expert
17/05/2018 - 01:43
Yes.
Excelchat Expert
17/05/2018 - 01:44
=VLOOKUP(M3,A:H,8,false)
User
17/05/2018 - 01:44
so for example M4 should equal H3:H14
Excelchat Expert
17/05/2018 - 01:44
Oh ok.
User
17/05/2018 - 01:45
Then can I drag that formula down the row to find the rest of the values in row 3?
Excelchat Expert
17/05/2018 - 01:46
Ok. I am working on it.
Excelchat Expert
17/05/2018 - 01:54
Give me few more minutes.
User
17/05/2018 - 01:54
ok
Excelchat Expert
17/05/2018 - 01:57
=sum(offset(indirect(address(match(M3,A:A,0),8)),0,0,12,1))
Excelchat Expert
17/05/2018 - 01:57
Please check it.
Excelchat Expert
17/05/2018 - 01:58
=sum(offset(indirect(address(match(M3,$A:$A,0),8)),0,0,12,1))
Excelchat Expert
17/05/2018 - 01:58
I have fixed column A in match function.
User
17/05/2018 - 01:59
I think this works!
User
17/05/2018 - 01:59
thank you for your help
Excelchat Expert
17/05/2018 - 01:59
You are welcome.
Excelchat Expert
17/05/2018 - 01:59
AS the formula is little complex, it took time.
Excelchat Expert
17/05/2018 - 02:00
Finally, could able to crack it.
Excelchat Expert
17/05/2018 - 02:00
Have wonderful day ahead.
Excelchat Expert
17/05/2018 - 02:00
Please do visit Got It Pro.
User
17/05/2018 - 02:00
thanks you as well
Excelchat Expert
17/05/2018 - 02:00
Please leave valuable feedback after closing the session
User
17/05/2018 - 02:03
Where in the formula is the reference to column H?
Excelchat Expert
17/05/2018 - 02:03
8
User
17/05/2018 - 02:04
and does that reference column "8" or does it lookup 8 column to the right of column A?
Excelchat Expert
17/05/2018 - 02:05
It is picking up 8.
User
17/05/2018 - 02:07
ok thanks again for your help!
Excelchat Expert
17/05/2018 - 02:07
address(match(M3,$A:$A,0),8)
Excelchat Expert
17/05/2018 - 02:08
It is the provides directly in column H.
User
17/05/2018 - 02:15
looking at that formula, if I wanted to pull just the singular value from that cell instead of the address, could I use =match(M3,$A:$A,0),8 ?
Excelchat Expert
17/05/2018 - 02:16
If you want single value. then it is easy.
Excelchat Expert
17/05/2018 - 02:16
=indirect(address(match(M3,$A:$A,0),8))
Excelchat Expert
17/05/2018 - 02:16
or just use vlookup.
User
17/05/2018 - 02:18
what would the vlookup formula be for that command?
Excelchat Expert
17/05/2018 - 02:18
v
Excelchat Expert
17/05/2018 - 02:18
=VLOOKUP(M3,A:H,8,false)
Excelchat Expert
17/05/2018 - 02:18
Thank you.
User
17/05/2018 - 02:25
Have a great day
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
