Two way lookup (Sumproduct or other faster way)

[snjpverma]

Source Data

	A	B	C
1	Employee	Item code.	Items Sold
2	A	1	80
3	B	1	90
4
5	A	2	110
6	B	2	120
7

<tbody>
</tbody>

Output

	F	G	H
1		1	2
2	A	80	110
3	B	90	120
4	C

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula G2 =SUMPRODUCT(($A$2:$A$7=$F2)*($B$2:$B$7=G$1)*$C$2:$C$7) H2 =SUMPRODUCT(($A$2:$A$7=$F2)*($B$2:$B$7=H$1)*$C$2:$C$7) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

I have used sumproduct function to get the number of items sold in G2 and H2. I would like to know if there is a faster way to do it? The sumproduct makes my worksheet quite slow as the data is quite huge.

 

[Aladin Akyurek]

Assuming that combinations such as A and 1 can occur more than once and the intention is to collect (sum) items sold (i.e. quanties), you can set up a range processing formula (with SUMIFS) instead of one that does array processing (like with SUMPRODUCT)...

In G2 just enter, copy across, and down:

=SUMIFS($C$2:$C$6,$A$2:$A$6,$A2,$B$2:$B$6,G$1)

This is also faster.

 

[Teeroy]

This looks to be a perfect candidate for a PivotTable.
Place the Employee field in the PivotTable Rows, Item Code in the Columns and Items Sold in Values (with Sum selected).

 

[snjpverma]

Assuming that combinations such as A and 1 can occur more than once and the intention is to collect (sum) items sold (i.e. quanties), you can set up a range processing formula (with SUMIFS) instead of one that does array processing (like with SUMPRODUCT)...

In G2 just enter, copy across, and down:

=SUMIFS($C$2:$C$6,$A$2:$A$6,$A2,$B$2:$B$6,G$1)

This is also faster.

Thanks Aladin, the intention is to retrieve the value not sum it. The combination will occur only once; however, I don't know how to get the vlookup or index and match working so i used sumproduct.

 

[snjpverma]

So, is there be a better function to be used, if the sum is not required ?

 

[Aladin Akyurek]

Thanks Aladin, the intention is to retrieve the value not sum it. The combination will occur only once; however, I don't know how to get the vlookup or index and match working so i used sumproduct.

So, is there be a better function to be used, if the sum is not required ?

In G2 control+shift+enter, not just enter, copy across, and down:

=IFNA(INDEX($C$2:$C$6,MATCH($F2,IF($B$2:$B$6=G$1,$A$2:$A$6),0)),"")

Note. If your Excel does not list IFNA, replace it in the formula with IFERROR.

 

[snjpverma]

Thanks Aladin. Does index/Match work faster than SUMIFS?

 

[snjpverma]

Since the Index-match version which you gave is an array formula whereas your SUMIFS version is a non-array formula. So I am confused as to which will be faster.

 

[Aladin Akyurek]

Since the Index-match version which you gave is an array formula whereas your SUMIFS version is a non-array formula. So I am confused as to which will be faster.

You can try to time these formulas. It's possibly the SUMIFS formula that is faster. Conceptually, the conditional INDEX/MATCH is the appropriate fit.