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

T

TheeUnderseer

New Member
Joined
Mar 16, 2017
Messages
4
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

*ABC

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #ffff00, align: left"]Finished Product No[/TD]
[TD="bgcolor: #ffff00, align: left"]Component Part Number[/TD]
[TD="bgcolor: #ffff00, align: left"]Component Qty[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: left"]Product 1[/TD]
[TD="align: left"]Comp1[/TD]
[TD="align: left"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: left"]Product 1[/TD]
[TD="align: left"]Comp2[/TD]
[TD="align: left"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: left"]Product 1[/TD]
[TD="align: left"]Comp3[/TD]
[TD="align: left"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: left"]Product 1[/TD]
[TD="align: left"]ABCComp1[/TD]
[TD="align: left"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: left"]Product 2[/TD]
[TD="align: left"]Comp4[/TD]
[TD="align: left"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: left"]Product 2[/TD]
[TD="align: left"]Comp5[/TD]
[TD="align: left"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: left"]Product 2[/TD]
[TD="align: left"]Comp6[/TD]
[TD="align: left"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: left"]Product 2[/TD]
[TD="align: left"]Comp7[/TD]
[TD="align: left"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: left"]Product 2[/TD]
[TD="align: left"]ABCComp1[/TD]
[TD="align: left"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: left"]Product 2[/TD]
[TD="align: left"]Comp8[/TD]
[TD="align: left"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: left"]Product 3[/TD]
[TD="align: left"]Comp9[/TD]
[TD="align: left"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: left"]Product 3[/TD]
[TD="align: left"]Comp10[/TD]
[TD="align: left"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: left"]Product 3[/TD]
[TD="align: left"]Comp11[/TD]
[TD="align: left"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: left"]Product 3[/TD]
[TD="align: left"]Comp12[/TD]
[TD="align: left"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: left"]Product 4[/TD]
[TD="align: left"]Comp13[/TD]
[TD="align: left"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: left"]Product 4[/TD]
[TD="align: left"]Comp14[/TD]
[TD="align: left"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: left"]Product 4[/TD]
[TD="align: left"]Comp15[/TD]
[TD="align: left"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: left"]Product 4[/TD]
[TD="align: left"]ABCComp2[/TD]
[TD="align: left"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: left"]Product 4[/TD]
[TD="align: left"]Comp17[/TD]
[TD="align: left"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="align: left"]Product 4[/TD]
[TD="align: left"]Comp18[/TD]
[TD="align: left"]5[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


Example Completed Output Table With Summary of ABCCompX Components

*ABCD
**

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #ffff00, align: left"]Finished Product No[/TD]
[TD="bgcolor: #ffff00, align: left"]ABC Part Number in Product? (Y/N)[/TD]
[TD="bgcolor: #ffff00, align: left"]ABC Part Number[/TD]
[TD="bgcolor: #ffff00, align: left"]ABC PN Qty[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: left"]Product 1[/TD]
[TD="align: left"]Y[/TD]
[TD="align: left"]ABCComp1[/TD]
[TD="align: left"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: left"]Product 2[/TD]
[TD="align: left"]Y[/TD]
[TD="align: left"]ABCComp1[/TD]
[TD="align: left"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: left"]Product 3[/TD]
[TD="align: left"]N[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: left"]Product 4[/TD]
[TD="align: left"]Y[/TD]
[TD="align: left"]ABCComp2[/TD]
[TD="align: left"]9[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Can't you just use a pivot table?

Excel Workbook
ABCD
11Finished Product NoComponent Part NumberComponent Qty
22Product 1Comp18
33Product 1Comp25
44Product 1Comp39
55Product 1ABCComp11
66Product 2Comp48
77Product 2Comp510
88Product 2Comp62
99Product 2Comp71
1010Product 2ABCComp16
1111Product 2Comp88
1212Product 3Comp95
1313Product 3Comp109
1414Product 3Comp119
1515Product 3Comp126
1616Product 4Comp134
1717Product 4Comp149
1818Product 4Comp151
1919Product 4ABCComp29
2020Product 4Comp173
2121Product 4Comp185
Sheet1


Then in a pivot table

Excel Workbook
LMNO
2Sum of Component QtyColumn Labels
3Row LabelsABCComp1ABCComp2Grand Total
4Product 1101
5Product 2606
6Product 3000
7Product 4099
8Grand Total7916
Sheet1
 
Last edited:
Upvote 0
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),"")
 
Upvote 0
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
ABCD
1Finished Product NoABC Part Number in Product? (Y/N)ABC Part NumberABC PN Qty
2Product 1YABCComp11
3Product 2YABCComp16
4Product 3N**
5Product 4YABCComp29
OutputTable
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Creating a dynamic list of unique values in a range with a condition
Replies
3
Views
201
Cubist
Cubist
smide
  • Solved
Formula for percentile if data are in multiple columns
Replies
2
Views
85
hagia_sofia
hagia_sofia
icytuvi
  • Solved
Join text in excel
2
Replies
15
Views
635
Burrgogi
Burrgogi
K
  • Question Question
Case sensitive distinct count based on multiple criteria
Replies
2
Views
261
shaowu459
shaowu459
P
  • Solved
Extract list using minimum as criteria
Replies
7
Views
281
Joe4
Joe4

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

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