Lookup Total in Pivot Table

[Naru2]

If you assume the data below is in a pivot table...Is there a vlookup formula to lookup a "Code" and then show the Total?
For example, if Code 111 is in the list, then return 10,000. Is this possible?


Product Code Units
Product ABC 111 1,000
Product ABC 222 2,000
Product ABC 333 3,000
Product ABC 444 4,000
Product ABC Total 10,000

 

[Vogateer]

That doesn't look like a place for a VLOOKUP formula, because you're not returning a value from the same row as the Code you're looking up.

But you can test for whether the Code exists in the table, and then return the total. For example:

F4 Formula, filled down the column as needed:

=IF(ISNUMBER(MATCH(E4,$B$4:$B$1000,0)), GETPIVOTDATA("Units", $A$3), "")

If I misunderstood, please let me know.

 

[Naru2]

So I think it works if there was only 1 data set in the pivot, but because I have multiple Products, it is actually pulling the Grand Total of all of them.
Is there a way to pull the Total for each subset? For example (using the new table below), for Code 111, the result would be 10,000. But for Code 555, the result would be 1,000.

Not sure if this is even possible.


Product Code Units
Product ABC 111 1,000
Product ABC 222 2,000
Product ABC 333 3,000
Product ABC 444 4,000
Product ABC Total 10,000
Product DEF 555 100
Product DEF 666 200
Product DEF 777 300
Product DEF 888 400
Product ABC Total 1,000

Thanks!

 

[Vogateer]

How would you identify which Total you want to use? If both totals say "Product ABC", then it's a real challenge to distinguish between those values. Is the only way to find it based on position, where the Total is below the Product Code? Have you tried to set up the PivotTable to give you the subtotals you want but it didn't work?

 

[Naru2]

How would you identify which Total you want to use? If both totals say "Product ABC", then it's a real challenge to distinguish between those values. Is the only way to find it based on position, where the Total is below the Product Code? Have you tried to set up the PivotTable to give you the subtotals you want but it didn't work?

That's a typo, I just manually created a fake pivot table, so the bottom line should be "Product DEF Total 1,000". My apologies.

 

[Vogateer]

Ah, in that case, I think this might do the trick.

Example Sheet:

F4 Formula, dragged down:

=IF(ISNUMBER(MATCH(E4,$B$4:$B$1000,0)), LET(product, INDEX($A$4:$A$1000, MATCH(E4, $B$4:$B$1000,0)), GETPIVOTDATA("Units",$A$3,"Product",product)), "")

Also, if anyone knows of a cleaner way to reference a PivotTable column, please let me know. Here I'm assuming your PivotTable doesn't have enough rows to go past row 1000, but you can adjust all those to be larger if needed.

 

[Iccreamann21]

Book1
	A	B	C	D	E	F
1	Product Code Units	Code	Unit
2	Product ABC	111	1000		Product ABC	10000
3	Product ABC	222	2000		Product DEF	1000
4	Product ABC	333	3000
5	Product ABC	444	4000
6	Product DEF	555	100
7	Product DEF	666	200
8	Product DEF	777	300
9	Product DEF	888	400
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=SUMIF(Table1[Product Code Units],E2,Table1[Unit])

 

[Naru2]

Ah, in that case, I think this might do the trick.

Example Sheet:
View attachment 113989

F4 Formula, dragged down:

=IF(ISNUMBER(MATCH(E4,$B$4:$B$1000,0)), LET(product, INDEX($A$4:$A$1000, MATCH(E4, $B$4:$B$1000,0)), GETPIVOTDATA("Units",$A$3,"Product",product)), "")

Also, if anyone knows of a cleaner way to reference a PivotTable column, please let me know. Here I'm assuming your PivotTable doesn't have enough rows to go past row 1000, but you can adjust all those to be larger if needed.

I'm confused by this part of the formula..."Product",product)),

Ah, in that case, I think this might do the trick.

Example Sheet:
View attachment 113989

F4 Formula, dragged down:

=IF(ISNUMBER(MATCH(E4,$B$4:$B$1000,0)), LET(product, INDEX($A$4:$A$1000, MATCH(E4, $B$4:$B$1000,0)), GETPIVOTDATA("Units",$A$3,"Product",product)), "")

Also, if anyone knows of a cleaner way to reference a PivotTable column, please let me know. Here I'm assuming your PivotTable doesn't have enough rows to go past row 1000, but you can adjust all those to be larger if needed.

This worked perfectly. You're a genius! Thank you.

 

[Vogateer]

I'm confused by this part of the formula..."Product",product)),
This worked perfectly. You're a genius! Thank you.

Are you familiar with the LET formula? It lets you take the result of a formula and give it a name, instead of typing it out repeatedly. Here I'm just using it because it's easier for me to read.

All that LET formula does in this case is calculate the formula:

=INDEX($A$4:$A$1000, MATCH(E4, $B$4:$B$1000,0))

, and then assign the value of that to the word product. For example, in the first cell with that formula, the INDEX-MATCH part gives me "Product ABC", and then makes product basically the same as "Product ABC". So that first GETPIVOTDATA formula becomes:

=GETPIVOTDATA("Units",$A$3,"Product","Product ABC")

.

Sometimes that LET formula also lets you avoid doing the same calculation multiple times, which can make things more efficient, but here I just wanted to make it easier to read.