Two way lookup (Sumproduct or other faster way)

[snjpverma]

Source Data

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Employee[/TD]
[TD]Item code.[/TD]
[TD]Items Sold[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]B[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]110[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]B[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]


Output

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]A[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]110[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]B[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.