maybe a complex lookup ?

[costin_chivu]

Hi. I want to create a sort of lookup by joining multiple ranges like in the table below:

A	B	C	D	E	F	G	H
1	product_name <tbody> </tbody>	sku_code <tbody> </tbody>			sku_code <tbody> </tbody>	qty <tbody> </tbody>
2	apples <tbody> </tbody>	c1 <tbody> </tbody>			c5 <tbody> </tbody>	200 <tbody> </tbody>
3	apples <tbody> </tbody>	c2 <tbody> </tbody>			c1 <tbody> </tbody>	300 <tbody> </tbody>
4	limes <tbody> </tbody>	c3 <tbody> </tbody>			c4 <tbody> </tbody>	350 <tbody> </tbody>
5	melons <tbody> </tbody>	c4 <tbody> </tbody>			c2 <tbody> </tbody>	100 <tbody> </tbody>
6	apples <tbody> </tbody>	c5 <tbody> </tbody>
7
8		total_qty <tbody> </tbody>
9	apples <tbody> </tbody>
10	limes <tbody> </tbody>
11	melons <tbody> </tbody>

<tbody>
</tbody>

What I want to achieve is the sum of quantities for all skus for all the products. C9 should be sum of C1, C2 and C5 quantities => 600. I can obtain an array of sku codes and then do a lookup ... but the lookup works for sorted ranges only... I have unsorted sku_codes list (f2:f5). Vlookup does not work with array lookup_value and I'm unable to cook a sumproduct that fits this example... I don't know how to imagine it.

Could you please help me to determine a formula without using a helper column sorting or VBA coding?

Many thanks,
Costin

 

[XOR LX]

Hi,

Perhaps this array formula** in C9:

=SUM(IF($B$2:$B$6=B9,N(OFFSET($G$2,MATCH($C$2:$C$6,$F$2:$F$5,0)-1,,,))))

Copy down as required.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).</SPAN></SPAN>

 

[VBA Geek]

just an alternative, with Control Shift enter

=SUMPRODUCT(0+ISNUMBER(MATCH($F$2:$F$5,IF($B$2:$B$6=$B9,$C$2:$C$6),0)),$G$2:$G$5)

 

[Aladin Akyurek]

Another...

product_name	sku_code			sku_code	qty
apples	c1			c5	200
apples	c2			c1	300
limes	c3			c4	350
melons	c4			c2	100
apples	c5
	total_qty
apples	600
limes	0
melons	350

<TBODY>
</TBODY>

B9, control+shift+enter, not just enter, and copy down:

=SUM(SUMIF($E$2:$E$5,IF($A$2:$A$6=A9,$B$2:$B$6),$F$2:$F$5))

 

[costin_chivu]

Thank you all. It works. That's really nice - I tried all but the simplest for me was Aladin's solution. Thanks again. Have a great day!