Using Index Match with SumProduct

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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
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]

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

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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