Vlookup

[mfbball04]

Need help with a formula for v lookup...

Original Data kind of looks like this..

Work order Part Number Scrap Code Amount Scrap
29135 49055 CDX 15
29135 49055 FTX 25
29135 49055 ABC 32
and so on....
trying to create a separate sheet so data can pull in by part number... like this..

Part Number Work Order ABC CDX FTX
29135 49055 32 15 25

 

[FormR]

Hi, maybe you can adapt one of these options to your two sheet set-up:

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
1	Work Order	Part Number	Scrap Code	Amount Scrap		Part Number	Work Order	ABC	CDX	FTX
2	29135	49055	CDX	15		29135	49055	32	15	25
3	29135	49055	FTX	25
4	29135	49055	ABC	32
5						Part Number	Work Order	ABC	CDX	FTX
6						29135	49055	32	15	25
Sheet1

Cell Formulas
Range	Formula
H2	=INDEX($D$2:$D$100,MATCH($F2&"|"&$G2&"|"&H$1,INDEX($A$2:$A$100&"|"&$B$2:$B$100&"|"&$C$2:$C$100,0),0))
H6	=SUMIFS($D:$D,$A:$A,$F6,$B:$B,$G6,$C:$C,H$5)

 

[mfbball04]

Dude, you're awesome... thank you SO much!

 

[mfbball04]

one more thing... in your above example what if there were multiple entries for "ABC" for work order 29135 and part number 49055, how would you work sum into that? thanks in advance

 

[mfbball04]

so say if there was another ABC for 25 right below it, my sheet with that formula is only reading the 32

 

[FormR]

how would you work sum into that?

Hi, that would be the SUMIFS() option demonstrated in range F5:J6 with the formula in H6 in post 2.

 

[DHayes]

You could try this one. paste in H2 and drag across. No Need for the sumif

=SUMPRODUCT(--($A$2:$A$5=$F2)*($B$2:$B$5=$G2)*($C$2:$C$5=H$1),$D$2:$D$5)

 

[FormR]

No Need for the sumif

HI, but why would one opt to use a less efficient alternative

 

[DHayes]

It seemed to work perfectly. Help me understand what you mean.

 

[FormR]

Help me understand what you mean.

Hi, SUMIFs() and the kindred COUNTIFs() are better (more efficient) for doing simple multi-conditional summing and counting. It is what they are designed to do.