Lookup w/ Multiple Criteria - one criteria is only first 3 characters of text string

[TheeUnderseer]

Hi MrExcel Forum,

This is my 1st post so I'll try to start on a good foot.

I receive product bill-of-materials (BOM) data in an input table and I need to summarize quantity of one type of high-cost components (ABCComp) per finished product in an output table.

Both tables list finished product part-numbers. The components I want to summarize are all prefixed "ABC" but are present in different quantities in different finished products, also some do not contain these ABCComp components at all.

I have tried using INDEX-MATCH without success and think that some form of array formula might be the way to go but am struggling to get one to work which only matches on the "ABC" at the beginning of my target components.

I appreciate all of the help and advice you can offer.

Many thanks, Tym



Example Input Table containing Bill-of-Materials data

*	A	B	C
1	Finished Product No	Component Part Number	Component Qty
2	Product 1	Comp1	8
3	Product 1	Comp2	5
4	Product 1	Comp3	9
5	Product 1	ABCComp1	1
6	Product 2	Comp4	8
7	Product 2	Comp5	10
8	Product 2	Comp6	2
9	Product 2	Comp7	1
10	Product 2	ABCComp1	6
11	Product 2	Comp8	8
12	Product 3	Comp9	5
13	Product 3	Comp10	9
14	Product 3	Comp11	9
15	Product 3	Comp12	6
16	Product 4	Comp13	4
17	Product 4	Comp14	9
18	Product 4	Comp15	1
19	Product 4	ABCComp2	9
20	Product 4	Comp17	3
21	Product 4	Comp18	5

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


Example Completed Output Table With Summary of ABCCompX Components

*	A	B	C	D
1	Finished Product No	ABC Part Number in Product? (Y/N)	ABC Part Number	ABC PN Qty
2	Product 1	Y	ABCComp1	1
3	Product 2	Y	ABCComp1	6
4	Product 3	N	*	*
5	Product 4	Y	ABCComp2	9

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Lobsterboy1]

Can't you just use a pivot table?

Excel Workbook
	A	B	C	D
1	1	Finished Product No	Component Part Number	Component Qty
2	2	Product 1	Comp1	8
3	3	Product 1	Comp2	5
4	4	Product 1	Comp3	9
5	5	Product 1	ABCComp1	1
6	6	Product 2	Comp4	8
7	7	Product 2	Comp5	10
8	8	Product 2	Comp6	2
9	9	Product 2	Comp7	1
10	10	Product 2	ABCComp1	6
11	11	Product 2	Comp8	8
12	12	Product 3	Comp9	5
13	13	Product 3	Comp10	9
14	14	Product 3	Comp11	9
15	15	Product 3	Comp12	6
16	16	Product 4	Comp13	4
17	17	Product 4	Comp14	9
18	18	Product 4	Comp15	1
19	19	Product 4	ABCComp2	9
20	20	Product 4	Comp17	3
21	21	Product 4	Comp18	5
Sheet1

Then in a pivot table

Excel Workbook
	L	M	N	O
2	Sum of Component Qty	Column Labels
3	Row Labels	ABCComp1	ABCComp2	Grand Total
4	Product 1	1	0	1
5	Product 2	6	0	6
6	Product 3	0	0	0
7	Product 4	0	9	9
8	Grand Total	7	9	16
Sheet1

 

[philaugust2004]

This is based on assumption that table 1 is a1 to c21
and table 2 I1 to L5

put this in l2 and copy down
=IF(J2="Y",SUMIFS($C$2:$C$21,$A$2:$A$21,I2,$B$2:$B$21,K2),"")

 

[TheeUnderseer]

Hi Lobsterboy1 & philaugust2004,

I really appreciate your feedback to my post. I realize there are many posts going up daily and this one could have easily been lost in the noise.

I should also have commented that the Output Table layout is fixed and will be used in this format by subsequent users. Also, the only data initially present in the Output Table are the Finished Product Nos.

So, I found an INDEX-MATCH array formula solution at How to Use INDEX+MATCH With Multiple Criteria in 5 Steps (How to Use INDEX+MATCH With
Multiple Criteria in 5 Easy Steps). Details below.

Many thanks, T

Excel Workbook
	A	B	C	D
1	Finished Product No	ABC Part Number in Product? (Y/N)	ABC Part Number	ABC PN Qty
2	Product 1	Y	ABCComp1	1
3	Product 2	Y	ABCComp1	6
4	Product 3	N	*	*
5	Product 4	Y	ABCComp2	9
OutputTable