# Sumproduct, Vlookup, Index and Match Array

#### Jeex87

##### New Member
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

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

##### MrExcel MVP
AA1 = 22
AA2 = 23

Control+shift+enter, not just enter:

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

#### Jeex87

##### New Member

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...

##### MrExcel MVP

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

##### New Member
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)

##### MrExcel MVP
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)

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))

Replies
3
Views
188
Replies
6
Views
312
Replies
13
Views
342
Replies
3
Views
156
Replies
5
Views
261

1,190,774
Messages
5,982,842
Members
439,799
Latest member
matts12

### 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.

### Which adblocker are you using?

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

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