Please help! Huge project for work: VLOOKUP Across Multiple Rows and Columns

[acholt17]

I am working on a project for work that involves over a thousand part numbers. Please refer to the tables below as examples:
Table 1: Main

Part #	Part Descrp.	plastic	paper	sticker	cloth	string
123	balloon
124	brush
125	CD
126	Pillow

<tbody>
</tbody>

Table 2: Materials

Part #	Part Descrp.	Pkg Items
123	balloon	plastic
123	balloon	string
124	brush	plastic
124	brush	paper
125	CD	plastic
125	CD	sticker
125	CD	paper
126	Pillow	cloth
126	Pillow	sticker

<tbody>
</tbody>

I need to take Table 1:Main and place an X every time a part number is used in Table 2: Materials. I need it to look like this:

Part #	Part Descrp.	plastic	paper	sticker	cloth	string
123	balloon	X				X
124	brush	X	X
125	CD	X	X	X
126	Pillow			X	X

<tbody>
</tbody>

Both Table 1: Main and Table 2: Materials are in different sheets because Table 1: Main has 466 rows while Table 2: Materials has 1212 rows. I have tried if statements, nested if statements within a vlookup, vlookup with match, etc. I tried =IF(AND($C4='Packaging Material'!$B$2:$B$1212, N$3='Packaging Material'!$F$2:$F$1212),"X", "") and it will only show a X on some of the packaging items (i.e. plastic, paper, sticker, cloth, string) but not on all of them.

Will someone please help me? I really do not want to manually put in the X's for that many rows of data.

 

[Weazel]

maybe something like...

Excel 2013

	A	B	C	D	E	F	G
1	Part #	Part Descrp.	plastic	paper	sticker	cloth	string
2	123	balloon	X				X
3	124	brush	X	X
4	125	CD	X	X	X
5	126	Pillow			X	X
6
7	Table 2: Materials
8	Part #	Part Descrp.	Pkg Items
9	123	balloon	plastic
10	123	balloon	string
11	124	brush	plastic
12	124	brush	paper
13	125	CD	plastic
14	125	CD	sticker
15	125	CD	paper
16	126	Pillow	cloth
17	126	Pillow	sticker

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula C2 =IF(SUMPRODUCT(($A$9:$A$17=$A2)*($B$9:$B$17=$B2)*($C$9:$C$17=C$1))>0,"X","") <tbody> </tbody>

<tbody>
</tbody>

 

[acholt17]

Thank you so much! This worked perfectly and saved me so much time! You are the best!

 

[Weazel]

you're welcome, glad it worked for you

 

[acholt17]

I was just informed that I need to put quantities in instead of X's. For example, in the worksheet above, I would have a packaging qty list starting row D8. If D9 were to equal 5 then C2 would equal 5 as well instead of an X. I am unsure how to rewrite the true part of the conditional for the program to pull the correct qty. Do you mind helping me once more?

 

[Canapone]

Hi,

quantities in d9:d17

=SUMPRODUCT(($A$9:$A$17=$A2)*($B$9:$B$17=$B2)*($C$9:$C$17=C$1)*$D$9:$D$17)


If do not need to see zeros

=IF(SUMPRODUCT(($A$9:$A$17=$A2)*($B$9:$B$17=$B2)*($C$9:$C$17=C$1)*$D$9:$D$17),SUMPRODUCT(($A$9:$A$17=$A2)*($B$9:$B$17=$B2)*($C$9:$C$17=C$1)*$D$9:$D$17),"")

or using first formula, format cells as

#.##0,-#.##0,


Hope it helps

 

[Weazel]

possibly something like...

Excel 2010

	A	B	C	D	E	F	G
1	Part #	Part Descrp.	plastic	paper	sticker	cloth	string
2	123	balloon	5	0	0	0	2
3	124	brush	3	4	0	0	0
4	125	CD	5	7	6	0	0
5	126	Pillow	0	0	9	8	0
6
7	Table 2: Materials
8	Part #	Part Descrp.	Pkg Items	Qty.
9	123	balloon	plastic	5
10	123	balloon	string	2
11	124	brush	plastic	3
12	124	brush	paper	4
13	125	CD	plastic	5
14	125	CD	sticker	6
15	125	CD	paper	7
16	126	Pillow	cloth	8
17	126	Pillow	sticker	9

<tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula C2 =SUMPRODUCT(($A$9:$A$17=$A2)*($B$9:$B$17=$B2)*($C$9:$C$17=C$1)*($D$9:$D$17)) <tbody> </tbody>

<tbody>
</tbody>

 

[acholt17]

How do I keep the cells that do not have any material from saying zero and just staying blank? Like if balloon only have 5 pieces of plastic, I want D2 through G2 to remain blank and not have a zero.


Thanks for responding so fast!

 

[acholt17]

Thank you both! I really appreciate both of you!