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:

Product # 1	Product #2	Product #3	Product #4
AA	BB
	CC		DD
EE	FF
		GG	HH

<TBODY>
</TBODY>

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

AA	100
BB
CC	200
DD	250
EE	300
FF	350
GG	400
HH	450

<TBODY>
</TBODY>

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
1	Product # 1	Product #2	Product #3	Product #4			AA	100
2	AA	BB			100		BB
3	CC		DD		450		CC	200
4	EE	FF			650		DD	250
5	GG	HH			850		EE	300
6							FF	350
7							GG	400
8							HH	450

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E2 =SUMPRODUCT( SUMIF(G$1:G$8, A2:D2, H$1:H$8)) <tbody> </tbody>

<tbody>
</tbody>

 

[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.