Vlookup

[TwinButte]

Hello,

I am making a lookup table that needs to gather data from from another sheet, the data is as follows (an example)

Field API Value

Earlie 14
Earlie 15
Earlie 18
Earlie 12
Kitscoty 18
Kitscoty 24
Primate 12
Primate 14

I need to have a lookup table that makes an average value for each fields API is this possible? there is a third column stating the type of fluid, gas oil etc. is there a way to only count oil API's?

 

[shyy]

So you want to avg all the numbers for say Earlie? Kitscoty? Primate?

Which excel you using? This works for excel 2007 +

Excel 2010

	A	B	C	D
Earlie	Earlie
Earlie	Kitscoty
Earlie	Primate
Earlie
Kitscoty
Kitscoty
Primate
Primate

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]14[/TD]

[TD="align: right"]14.75[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]15[/TD]

[TD="align: right"]21[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]18[/TD]

[TD="align: right"]13[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]=AVERAGEIF($A$1:$A$8,C1,$B$1:$B$8)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=AVERAGEIF($A$1:$A$8,C2,$B$1:$B$8)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]=AVERAGEIF($A$1:$A$8,C3,$B$1:$B$8)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[TwinButte]

That will work great thank you i never even though of doing it that way

 

[shyy]

Yeah, I don't think I ever used this function till now. Glad it worked.