Summing different VLOOKUP values - one table array, multiple lookup columns

[ianalyzethings]

I'm trying to sum different vlookup functions using one table array and I'm getting #N/A each.

I'm using just one table array but multiple lookup values along the same row (without getting into too much detail, each product in same row constitutes an equivalent sku to the other products in that same row).

To illustrate, let's say these are the lookup values:

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Product # 1[/TD]
[TD]Product #2[/TD]
[TD]Product #3[/TD]
[TD]Product #4[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]BB[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CC[/TD]
[TD][/TD]
[TD]DD[/TD]
[/TR]
[TR]
[TD]EE[/TD]
[TD]FF[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]GG[/TD]
[TD]HH[/TD]
[/TR]
</TBODY>[/TABLE]

And here's the table array, showing unit sales per product (note BB is blank intentionally):

[TABLE="width: 500"]
<TBODY>[TR]
[TD]AA[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]DD[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]EE[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]FF[/TD]
[TD]350[/TD]
[/TR]
[TR]
[TD]GG[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]HH[/TD]
[TD]450[/TD]
[/TR]
</TBODY>[/TABLE]


I want to add up totals for equivalent skus. Based on the above, AA is equiv to BB, CC equiv to DD, EE equiv to FF, GG equiv to HH.

So I tried a simply summing of vlookups: VLOOKUP(Product #1,table array,2,FALSE)+VLOOKUP(Product #1 column, table array,2,FALSE)+VLOOKUP(Product #3,table array,2,FALSE)+VLOOKUP(Product #4,table array,2,FALSE).

I'm getting #N/A for everything. I'm guessing the blanks in either the table array or lookup columns are causing problems, but I've tried adding 0s and it's still not working.

 

[circledchicken]

Hi,

You could use SUMIF instead of VLOOKUP, or wrap the VLOOKUPs in IFERROR functions to return zero's.

Alternatively, you can try an approach like this (adjust the ranges as required):

Excel 2013

	A	B	C	D	E	F	G	H
Product # 1	Product #2	Product #3	Product #4	AA
AA	BB	BB
CC	DD	CC
EE	FF	DD
GG	HH	EE
FF
GG
HH

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]100[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]450[/TD]
[TD="align: right"][/TD]

[TD="align: right"]200[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]650[/TD]
[TD="align: right"][/TD]

[TD="align: right"]250[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]850[/TD]
[TD="align: right"][/TD]

[TD="align: right"]300[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]350[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]400[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]450[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=SUMPRODUCT(
SUMIF(G$1:G$8, A2:D2, H$1:H$8))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[ianalyzethings]

Worked perfectly. And it's a bonus any time I don't have to use the dreaded VLOOKUP.

Thanks for your help.

 

[circledchicken]

You're welcome - thanks for the feedback.