CalculateAv Average, Std Dev, Max, Min, using Index/Match

[arnorian]

Hi,

So, I have a sheet with SKU/Item numbers and another sheet with just SKU/Item numbers where i need to fill average, std. dev. max, min and mode . I need to use index/match to the Item number in that other sheet and only then calculate an average or standard deviation or any other trick that may work best.

I have used different variations of Index/Match and as well as Stddev with IF statement to no avail. Here's a sample formula I have been using:

=STDEV(IF('Sheet1'!D8:D8676='Sheet2'!A2),Sheet1T!G8:G8676)

I was able to use AVERAGEIF with no problem but there's no similar function for STDDEV or MAX or MIN

 

[sheetspread]

Subtotal and pivot tables will give you this info, do you absolutely need a formula?

 

[sheetspread]

Stddev with IF statement to no avail

Excel 2010
	A	B	C	D
1	Name	Number
2	A	1
3	B	5
4	C	7
5	A	2
6	E	3
7	F	8
8	G	9
9	A	6
10
11
12				2.645751
13
14				2.645751
Sheet4

Cell Formulas
Range	Formula
D14	=STDEV(B2,B5,B9)
D12	{=STDEV(IF($A$2:$A$9=A9,$B$2:$B$9))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[arnorian]

Thank you. Initially they wanted to be a Index/Match. but after completed with pivot table they were satisfied.

Thank you for everyone's help