Hi All,
I appreciate the time for reading this post. I've been trying to nud out this problem I have. I am fairly new to excel, but tried my best.
I am basically trying to create a a running number based on a selected accounting period.
For example, when selecting period 1, the YTD amount will vlookup column 22. When I select period 2, the YTD amount will vlookup and sum columns 22 and 23. So on and so on.
I can get the vlookup formula to work with the following:
=SUMPRODUCT(VLOOKUP(P6,'RAW DATA'!A:W,{22,23},FALSE))
The problem is, I have manually typed in the {22,23}.
The main formula I use is:
=SUMPRODUCT(VLOOKUP(P6,'RAW DATA'!A:AW,(INDEX(W:AA,MATCH($V$1,W:W,0),5)),FALSE)*-1)
This works well for period 1 (value in cell AA1 is 22). But when selecting period 2 (value in cell AA2 is 22,23) I get a #REF error.
I have tried renaming cell AA2 to "{22,23}" but get the same result...
What am I doing wrong
I appreciate the time for reading this post. I've been trying to nud out this problem I have. I am fairly new to excel, but tried my best.
I am basically trying to create a a running number based on a selected accounting period.
For example, when selecting period 1, the YTD amount will vlookup column 22. When I select period 2, the YTD amount will vlookup and sum columns 22 and 23. So on and so on.
I can get the vlookup formula to work with the following:
=SUMPRODUCT(VLOOKUP(P6,'RAW DATA'!A:W,{22,23},FALSE))
The problem is, I have manually typed in the {22,23}.
The main formula I use is:
=SUMPRODUCT(VLOOKUP(P6,'RAW DATA'!A:AW,(INDEX(W:AA,MATCH($V$1,W:W,0),5)),FALSE)*-1)
This works well for period 1 (value in cell AA1 is 22). But when selecting period 2 (value in cell AA2 is 22,23) I get a #REF error.
I have tried renaming cell AA2 to "{22,23}" but get the same result...
What am I doing wrong