Using Index Match with SumProduct

[MLSNetworks]

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!

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

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[C Moore]

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

 

[MLSNetworks]

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

Pivot #1
Date	Deal Name	Deal Value
6/6/2019	DOT Deal	320000
6/6/2019	Offnet Deal	375000
6/6/2019	Energy Deal	100000
6/6/2019	Blue Deal	500000
Pivot #2
Date	Deal Name	Deal Value
5/30/2019	DOT Deal	160000
5/30/2019	Energy Deal	400000
5/30/2019	Johnson Deal	20000
5/30/2019	Offnet Deal	300000
5/30/2019	Franks Deal	2000000

<colgroup><col><col><col></colgroup><tbody>
</tbody>

_{^{<strike>
</strike>}}[/FONT]

 

[Twollaston]

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!

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

<tbody>
</tbody>

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

Week 1			Week 2
Deal Name	Deal Value			Deal Name	Deal Value		Answers
A	171			G	243		42
B	87			F	52		-94
C	339			E	325		237
D	204			D	334		130
E	88			C	260		-79
F	146			B	357		270
G	201			A	33		-138

<tbody>
</tbody>

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

 

[MLSNetworks]

Thank you so much, Twollaston! That works perfect and is exactly what I needed

 

[Twollaston]

Thank you so much, Twollaston! That works perfect and is exactly what I needed

No problem, Happy to help you!