Lookup Formula?

[Katich]

Hello,

I have a report which needs data populated into it from a downloaded file. My report looks a little like this. I need to pull the data from the second table into this report. I was going to do a lookup but since the Part# duplicates i knew it wasn't going to be that easy. So since Row one is what determines what each station result is, i thought maybe an IF statement with a lookup within. But then i started just getting confused. Any help would be greatly appreciated.

Thank you!

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]P010[/TD]
[TD]P010A[/TD]
[TD]P010B[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Part#[/TD]
[TD]Station 1[/TD]
[TD]Station 2[/TD]
[TD]Station 3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12345[/TD]
[TD]430[/TD]
[TD]185[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The report my data comes from looks like this

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Part#[/TD]
[TD]Spec#[/TD]
[TD]Description[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]12345[/TD]
[TD]P010[/TD]
[TD]430[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12345[/TD]
[TD]P010A[/TD]
[TD]185[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]12345[/TD]
[TD]P010B[/TD]
[TD]75[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

I assume if the part number/Spec number is not found you want to return blank.

Excel 2010
	A	B	C	D
1	Part#	Spec#	Description
2	12345	P010	430
3	12345	P010A	185
4	12345	P010B	75
5	77796	P010A	77
6
7
8
9		P010	P010A	P010B
10	Part#	Station 1	Station 2	Station 3
11	12345	430	185	75
12	77796		77
Sheet1

Cell Formulas
Range	Formula
B11	{=IFERROR(INDEX($C$2:$C$5,MATCH($A11&B$9,$A$2:$A$5&$B$2:$B$5,0)),"")}
B12	{=IFERROR(INDEX($C$2:$C$5,MATCH($A12&B$9,$A$2:$A$5&$B$2:$B$5,0)),"")}
C11	{=IFERROR(INDEX($C$2:$C$5,MATCH($A11&C$9,$A$2:$A$5&$B$2:$B$5,0)),"")}
C12	{=IFERROR(INDEX($C$2:$C$5,MATCH($A12&C$9,$A$2:$A$5&$B$2:$B$5,0)),"")}
D11	{=IFERROR(INDEX($C$2:$C$5,MATCH($A11&D$9,$A$2:$A$5&$B$2:$B$5,0)),"")}
D12	{=IFERROR(INDEX($C$2:$C$5,MATCH($A12&D$9,$A$2:$A$5&$B$2:$B$5,0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[VBA Geek]

If I got it correctly, this should do it

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1		P010	P010A	P010B			Part#	Spec#	Description
2	Part#	Station 1	Station 2	Station 3			12345	P010	430
3	12345	430	185	75			12345	P010A	185
4							12345	P010B	75
5
6
Sheet1

Cell Formulas
Range	Formula
B3	=SUMIFS($I$2:$I$4,$G$2:$G$4,$A3,$H$2:$H$4,B$1)
C3	=SUMIFS($I$2:$I$4,$G$2:$G$4,$A3,$H$2:$H$4,C$1)
D3	=SUMIFS($I$2:$I$4,$G$2:$G$4,$A3,$H$2:$H$4,D$1)

 

[Katich]

Worked great!! Thank you!

 

[Katich]

Thank you!!