I want to look up a number in column A and return column B and C if it is there. So, on another worksheet I'd input FG30827110 and have four items returned (Each item in column B and C returned).
-
Advanced Filter?– Scott CranerNov 6, 2018 at 17:17
-
Maybe. Let me add clarity. I'll need to know that if I have to build 4 pcs of the FG#, I will need 8 pcs of the PC00065 (because it is 2 per). But another set of cells might call out 3 pcs per and I'd need 12 total PC00065. In the end, I need to know total quantity of column B with varying quantities of column A. Does that make sense?– Nick ScolaroNov 6, 2018 at 18:00
-
It sounds like the formula for column b is something like (if "a2=x" then "y") and column c is similarly setup. I would add another column for "number of pc's" and modify column c to multiply its output by that new column. Basically A2=FG30827110 --> B2=PC00065 --> C2(# of PCs)=2 --> D2=B2*C2 which would output 4.– AndrewNov 6, 2018 at 18:18
-
@Nick Scolaro - please edit your question instead of using comments to add information– cybernetic.nomadNov 6, 2018 at 18:25
Add a comment
|
1 Answer
You can use an array formula to pull the component item
=INDEX(Table1[parent item no],MATCH(A1,Table1[child item no],0))
(this must be entered with CTRL+SHIFT+ENTER).
Then use SUMIFS to get the quantity
=SUMIFS(Table1[pts per],Table1[parent item no],A1,Table1[child item no],b1)*(qty needed)
