Sumproduct, Vlookup, Index and Match Array

[Jeex87]

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

 

[Aladin Akyurek]

AA1 = 22
AA2 = 23

Control+shift+enter, not just enter:

=SUM(VLOOKUP(P6,'RAW DATA'!A:W,$AA$1:$AA$2,0))

 

[Jeex87]

Hi Aladin,

Thanks for your reply. But unfortunately I still have the issue...I think it must have something to do with it not recognizing the value that should be in the column index is {22,23} which is the value of the cell it is looking up...

 

[Aladin Akyurek]

Hi Aladin,

Thanks for your reply. But unfortunately I still have the issue...I think it must have something to do with it not recognizing the value that should be in the column index is {22,23} which is the value of the cell it is looking up...

What do you have in AA1 and AA2 literally?

 

[Jeex87]

AA1 I have 22
AA2 I have {22,23}

AA1 works of course, because it is just 22....Its the range I want {22,23} that is causing me an issue in regards to selecting the column index number with that formula using the Index & Match to find the column index: =SUMPRODUCT(VLOOKUP(P6,'RAW DATA'!A:AW,(INDEX(W:AA,MATCH($V$1,W:W,0),5)),FALSE)*-1)

Thank you for your help in advance

 

[Aladin Akyurek]

AA1 I have 22
AA2 I have {22,23}

AA1 works of course, because it is just 22....Its the range I want {22,23} that is causing me an issue in regards to selecting the column index number with that formula using the Index & Match to find the column index: =SUMPRODUCT(VLOOKUP(P6,'RAW DATA'!A:AW,(INDEX(W:AA,MATCH($V$1,W:W,0),5)),FALSE)*-1)

Thank you for your help in advance

If you insist on having {22,23} in AA2, add the following function code to your workbook, using Alt+F11 followed by Insert | Module...

Public Function EVAL(theInput As Variant) As Variant
'
' if UDF evaluate the input string as though it was on this sheet
' else evaluate for activesheet
'
Dim vEval As Variant
Application.Volatile
On Error GoTo funcfail
If Not IsEmpty(theInput) Then
If TypeOf Application.Caller.Parent Is Worksheet Then
vEval = Application.Caller.Parent.Evaluate(CStr(theInput))
Else
vEval = Application.Evaluate(CStr(theInput))
End If
If IsError(vEval) Then
EVAL = CVErr(xlErrValue)
Else
EVAL = vEval
End If
End If
Exit Function
funcfail:
EVAL = CVErr(xlErrNA)
End Function

Control+shift+enter, not just enter:

=SUM(VLOOKUP($P6,'RAW DATA'!$A:$W,EVAL(AA$1),0))