Can Vlookup be used in an array function like sumproduct?

bbhgroup

Board Regular
Joined
Dec 30, 2008
Messages
97
I have columns of data. I want to be able to walk across the columns horizontally and count the number of items that are members of a group using the vlookup function

Cols:
A B C D E F G H (up to 256..)
10 MM 1-1/2GR 20 MM 3/4 STONE (Row 1)
Targ. Act. Targ. Act. Targ. Act. Targ. Act. (Row 2)
1000 1000 2000 2050 4000 4100 1400 1350 (Row 3)
1200 1200 2100 2000 4000 3900 1500 1400 (Row 4)

etc.

The lookup list is (it has the name "Mat_by_Group"):

10 MM "A"
1-1/2 GR "B"
20 MM "A"
3/4 STONE "C"

Ultimately what I want to do is sum the "Targ." columns of the materials that belong to the same group per row. For the above data, I would get
For Row 1
"A" = 5100 "B" = 2000 "C"= 1400
For Row 2
"A" = 5200 "B" = 1200 "C" = 1500

I've tried to use Sumproduct with three arrays to derive the function:
The first array is the data
The second array is to select every other column
The third array is to select the group

=SUMPRODUCT(OFFSET($A:A,0,0,1,256),IF(MOD(COLUMN($A:$IV),2)=1,1,0),IF(VLOOKUP(OFFSET($A:A,0,0,1,256),Mat_By_Group,1,FALSE)="A",1,0)

But I am getting back a #Value! error.
Can anyone tell me if I'm on the right track?

Thanks.
 
Last edited:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
There were a few errors in your post so it took me quite a while to figure out how you were arriving at your totals. "A" for row one, given your sample, equals 5000, not 5100. And it is not even row 1, it is row 2. The header is row 1.

What will make your data considerably easier to manage is to organize it into 3 columns and then just run your sumproduct() summaries on that

Col A: Description followed by your single character (example: 10 MM A, or 20 MM A)
Col.B: Target
Col.C: Act(ual)

In that case, your formula would be

=SUMPRODUCT((RIGHT(A2:A10000)="A")*(C2:C10000))
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top