maybe a complex lookup ?

C

costin_chivu

New Member
Joined
May 6, 2012
Messages
4
Hi. I want to create a sort of lookup by joining multiple ranges like in the table below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR="class: grid"]
[TD="width: 121"]product_name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR="class: grid"]
[TD="width: 86"]sku_code[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]sku_code[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]qty[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR="class: grid"]
[TD="width: 121"]apples[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR="class: grid"]
[TD="width: 86"]c1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]c5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64, align: right"]200[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR="class: grid"]
[TD="width: 121"]apples[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR="class: grid"]
[TD="width: 86"]c2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]c1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64, align: right"]300[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR="class: grid"]
[TD="width: 121"]limes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR="class: grid"]
[TD="width: 86"]c3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]c4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64, align: right"]350[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR="class: grid"]
[TD="width: 121"]melons[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR="class: grid"]
[TD="width: 86"]c4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]c2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64, align: right"]100[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR="class: grid"]
[TD="width: 121"]apples[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR="class: grid"]
[TD="width: 86"]c5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][TABLE="width: 86"]
<tbody>[TR="class: grid"]
[TD="width: 86"]total_qty[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR="class: grid"]
[TD="width: 121"]apples[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR="class: grid"]
[TD="width: 121"]limes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR="class: grid"]
[TD="width: 121"]melons[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
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>
 
Last edited:
Upvote 0
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)
 
Upvote 0
Another...

[TABLE="width: 288"]
<TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]product_name
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]sku_code
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]sku_code
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]qty
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]apples
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]c1
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]c5
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]200
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]apples
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]c2
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]c1
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]300
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]limes
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]c3
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]c4
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]350
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]melons
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]c4
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]c2
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]100
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]apples
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]c5
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]total_qty
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]apples
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]600
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]limes
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]melons
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]350
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

B9, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=SUM(SUMIF($E$2:$E$5,IF($A$2:$A$6=A9,$B$2:$B$6),$F$2:$F$5))
 
Upvote 0
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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Help on array formula conversion
Replies
3
Views
685
dmark224
D
C
  • Solved
Sumproduct with multiple rows and lookup second array column criteria
2
Replies
12
Views
4K
KRice
K
G
  • Question Question
Complex Nested Array Formula - Sumproduct with Offset rows
Replies
9
Views
2K
Aladin Akyurek
Aladin Akyurek
D
  • Question Question
SUMPRODUCT with INDEX & MATCH
2
Replies
19
Views
49K
Rahul1987
R
I
  • Question Question
Summing different VLOOKUP values - one table array, multiple lookup columns
Replies
3
Views
4K
circledchicken
C

Forum statistics

Threads
1,223,630
Messages
6,173,453
Members
452,514
Latest member
cjkelly15

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top