Using Index Match with SumProduct

M

MLSNetworks

New Member
Joined
Jun 6, 2019
Messages
17
Hello- I have two pivot tables from different weeks that consist of date, a sales deal name and the value of the deal. I need to see the changes in values on each deal that have occurred from week to week.

In the table below, I want to create a formula in a column outside of Pivot Table #1 labeled "Difference Increase/(Decrease)" which is the deal value in Pivot Table #1 minus the deal value in Pivot Table #2 . The tables below are examples but my two pivot tables consist of multiple deals that are not in the same rows so I can't just do a minus formula and drag it down, instead I am having to manually hunt and peck to find the correct deal name and manually minus the deal value columns to see the change in deal value from week to week. What is the best formula to use? Can I use Index Match and SumProduct in an array formula to get the minus calculation that I can autofill down? I don't know how to set that up and I'm just guessing on what the best formula is to use so any instruction would be greatly appreciated! Thank you in advance!

[TABLE="width: 424"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pivot Table #1 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Deal Name[/TD]
[TD]Deal Value[/TD]
[/TR]
[TR]
[TD]6/6/2019[/TD]
[TD][TABLE="width: 105"]
<colgroup><col></colgroup><tbody>[TR]
[TD]DOT Deal[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]320000[/TD]
[/TR]
[TR]
[TD]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] 22222]6/6/2019[/COLOR]​
<strike></strike>
[/TD]
[TD]Offnet Deal[/TD]
[TD]375000[/TD]
[/TR]
[TR]
[TD]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] 22222]6/6/2019[/COLOR]​
<strike></strike>
[/TD]
[TD]Energy Deal[/TD]
[TD]100000[/TD]
[/TR]
[TR]
[TD]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] 22222]6/6/2019[/COLOR]​
<strike></strike>
[/TD]
[TD]Blue Deal[/TD]
[TD]500000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pivot Table #2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Deal Name[/TD]
[TD]Deal Value[/TD]
[/TR]
[TR]
[TD]5/30/2019[/TD]
[TD]DOT Deal[/TD]
[TD]160000[/TD]
[/TR]
[TR]
[TD]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] 22222]5/30/2019[/COLOR]​
<strike></strike>
[/TD]
[TD]Energy Deal[/TD]
[TD]400000[/TD]
[/TR]
[TR]
[TD]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] 22222]5/30/2019[/COLOR]​
<strike></strike>
[/TD]
[TD]Johnson Deal[/TD]
[TD]20000[/TD]
[/TR]
[TR]
[TD]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] 22222]5/30/2019[/COLOR]​
<strike></strike>
[/TD]
[TD]Offnet Deal[/TD]
[TD]300000[/TD]
[/TR]
[TR]
[TD]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] 22222]5/30/2019[/COLOR]​
<strike></strike>
[/TD]
[TD]Franks Deal[/TD]
[TD]2000000[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Working outside the tables you could use sumproduct with large inside. Something like:
SUMPRODUCT(
LARGE(
(([cell ref for Deal name in Table 1]=[array of data for deal name])+0)*[array of Date data],2),[array of Deal Values]) *limits dates relating to specific deal and finds the 2nd greatest, then returns the deal value
Add another column to subtrract the differences
 
Upvote 0
Hi C Moore: Thank you for your reply. This did not work for me. Here is my formula based on the two pivot tables. The deal name cell ref is in cell B3 and the array of data for deal name is B3:B6. The array of the deal values is C3:C6. I want the formula to show the difference in deal values between the two pivot tables.
What am I missing/doing wrong? ​
Thanks for your help.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=SUMPRODUCT(LARGE((([B3]=[B3:B6])+0)*[A3:A6],2),[C3:C6])

[TABLE="width: 347"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="align: left"]Pivot #1[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]Date
[/TD]
[TD="align: left"]Deal Name[/TD]
[TD="align: left"]Deal Value[/TD]
[/TR]
[TR]
[TD]6/6/2019[/TD]
[TD="align: left"]DOT Deal
[/TD]
[TD="align: right"]320000[/TD]
[/TR]
[TR]
[TD]6/6/2019[/TD]
[TD="align: left"]Offnet Deal[/TD]
[TD="align: right"]375000[/TD]
[/TR]
[TR]
[TD]6/6/2019[/TD]
[TD="align: left"]Energy Deal[/TD]
[TD="align: right"]100000[/TD]
[/TR]
[TR]
[TD]6/6/2019[/TD]
[TD="align: left"]Blue Deal[/TD]
[TD="align: right"]500000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Pivot #2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]Date
[/TD]
[TD="align: left"]Deal Name[/TD]
[TD="align: left"]Deal Value[/TD]
[/TR]
[TR]
[TD]5/30/2019[/TD]
[TD="align: left"]DOT Deal[/TD]
[TD="align: right"]160000[/TD]
[/TR]
[TR]
[TD]5/30/2019[/TD]
[TD="align: left"]Energy Deal[/TD]
[TD="align: right"]400000[/TD]
[/TR]
[TR]
[TD]5/30/2019[/TD]
[TD="align: left"]Johnson Deal[/TD]
[TD="align: right"]20000[/TD]
[/TR]
[TR]
[TD]5/30/2019[/TD]
[TD="align: left"]Offnet Deal[/TD]
[TD="align: right"]300000[/TD]
[/TR]
[TR]
[TD]5/30/2019[/TD]
[TD="align: left"]Franks Deal[/TD]
[TD="align: right"]2000000[/TD]
[/TR]
</tbody>[/TABLE]
<strike>
</strike>[/FONT]
 
Upvote 0
MLSNetworks said:
Hello- I have two pivot tables from different weeks that consist of date, a sales deal name and the value of the deal. I need to see the changes in values on each deal that have occurred from week to week.

In the table below, I want to create a formula in a column outside of Pivot Table #1 labeled "Difference Increase/(Decrease)" which is the deal value in Pivot Table #1 minus the deal value in Pivot Table #2 . The tables below are examples but my two pivot tables consist of multiple deals that are not in the same rows so I can't just do a minus formula and drag it down, instead I am having to manually hunt and peck to find the correct deal name and manually minus the deal value columns to see the change in deal value from week to week. What is the best formula to use? Can I use Index Match and SumProduct in an array formula to get the minus calculation that I can autofill down? I don't know how to set that up and I'm just guessing on what the best formula is to use so any instruction would be greatly appreciated! Thank you in advance!

[TABLE="width: 424"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pivot Table #1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Deal Name[/TD]
[TD]Deal Value[/TD]
[/TR]
[TR]
[TD]6/6/2019[/TD]
[TD][TABLE="width: 105"]
<tbody>[TR]
[TD]DOT Deal[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]320000[/TD]
[/TR]
[TR]
[TD]
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2"]#2[/URL] 22222]6/6/2019[/COLOR]​
<strike></strike>[/TD]
[TD]Offnet Deal[/TD]
[TD]375000[/TD]
[/TR]
[TR]
[TD]
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2"]#2[/URL] 22222]6/6/2019[/COLOR]​
<strike></strike>[/TD]
[TD]Energy Deal[/TD]
[TD]100000[/TD]
[/TR]
[TR]
[TD]
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2"]#2[/URL] 22222]6/6/2019[/COLOR]​
<strike></strike>[/TD]
[TD]Blue Deal[/TD]
[TD]500000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pivot Table #2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Deal Name[/TD]
[TD]Deal Value[/TD]
[/TR]
[TR]
[TD]5/30/2019[/TD]
[TD]DOT Deal[/TD]
[TD]160000[/TD]
[/TR]
[TR]
[TD]
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2"]#2[/URL] 22222]5/30/2019[/COLOR]​
<strike></strike>[/TD]
[TD]Energy Deal[/TD]
[TD]400000[/TD]
[/TR]
[TR]
[TD]
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2"]#2[/URL] 22222]5/30/2019[/COLOR]​
<strike></strike>[/TD]
[TD]Johnson Deal[/TD]
[TD]20000[/TD]
[/TR]
[TR]
[TD]
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2"]#2[/URL] 22222]5/30/2019[/COLOR]​
<strike></strike>[/TD]
[TD]Offnet Deal[/TD]
[TD]300000[/TD]
[/TR]
[TR]
[TD]
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2"]#2[/URL] 22222]5/30/2019[/COLOR]​
<strike></strike>[/TD]
[TD]Franks Deal[/TD]
[TD]2000000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

What you want to do is create a vlookup to pull the value you need in week 1, and then subtract that amount from the value you have for week 2

[TABLE="class: cms_table, width: 650"]
<tbody>[TR]
[TD="colspan: 2"]Week 1[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Week 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Deal Name[/TD]
[TD]Deal Value[/TD]
[TD][/TD]
[TD][/TD]
[TD]Deal Name[/TD]
[TD]Deal Value[/TD]
[TD][/TD]
[TD]Answers[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]171[/TD]
[TD][/TD]
[TD][/TD]
[TD]G[/TD]
[TD="align: right"]243[/TD]
[TD][/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]87[/TD]
[TD][/TD]
[TD][/TD]
[TD]F[/TD]
[TD="align: right"]52[/TD]
[TD][/TD]
[TD="align: right"]-94[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]339[/TD]
[TD][/TD]
[TD][/TD]
[TD]E[/TD]
[TD="align: right"]325[/TD]
[TD][/TD]
[TD="align: right"]237[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]204[/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD="align: right"]334[/TD]
[TD][/TD]
[TD="align: right"]130[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]88[/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD="align: right"]260[/TD]
[TD][/TD]
[TD="align: right"]-79[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]146[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD="align: right"]357[/TD]
[TD][/TD]
[TD="align: right"]270[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD="align: right"]201[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD="align: right"]-138[/TD]
[/TR]
</tbody>[/TABLE]



FORMULA
=F3-VLOOKUP(E3,$A$3:$B$100,2,FALSE)

obviously you don't want to put the exact same formula as me because the cells i'm using are not the exact cells of you pivot table

F3 is week 2 amount for deal G
E3 is week 2 for whatever the first deal is(probably not deal A since you said they get out of order) in my example it is deal G
$A$3:$B$100 is the range for week 1 pivot table, might want to make it longer than it needs to be so that it will update when more stuff is added, or instead of using the cell names like A2 just grab those cells from the pivot table, and should automate it for you something like get pivot table data, that way you always catch all the rows
the vlookup takes cell E3 (week 2 deal name) and finds the corresponding deal value from week 1
and so once you have it pulling the correct value for week 1, you just want to do week 2 - week 1 = change

Which is already handeled in the formula
The VLOOKUP(E3,$A$3:$B$100,2,FALSE) will return the value 201 the week 1 value of deal G
and the formula F3 is 243 the week 2 value of G
so the formula states:
243-201 and returns to you G Deal week 2 value - G deal Week 1 value = 42
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Web Query in VBA Loop through tables on page to choose get tags from table instead of text
2
Replies
13
Views
568
Domenic
D
S
  • Question Question
Keep getting an "Error 1004: item could not be found in the OLAP cube" when applying the filter to pivot table
Replies
5
Views
1K
RoryA
RoryA
S
  • Solved
Get table name on worksheet using formulas without using VBA
2
Replies
11
Views
1K
6StringJazzer
6StringJazzer
Kellie220
  • Solved
Formula for calculating different dates/ VBA
Replies
3
Views
659
awoohaw
awoohaw
N
  • Question Question
What should I do in order to solve an inconsistent reference error when using INDEX/MATCH?
Replies
1
Views
341
numbernoodle
N

Forum statistics

Threads
1,223,162
Messages
6,170,432
Members
452,326
Latest member
johnshaji

Share this page

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