Look up name to calculate its corresponding front value, AverageIf, Index, And Match

[major_johnson]

Friends,

I have two columns:

- Different types of names repeated
- Values next to the names

I would like to request to have a formula that would look up the name then return the average for its values.

I think the file would explain better.

Here is a simple illustration of what I mean:

Oranges: 1000
Oranges: 3000
Oranges: 900
Cakes: 1900
Cakes: 1300
Cakes: 1500
Apples: 1300
Apples: 5000
Apples: 7000
Apples: 5000
Apples: 1000

A blank cell anywhere in the sheet, whatever name is typed into it would return the average value for the correspondence.

I think this blank cell would require a mixture of index and match with averageif.

Thank you very much in advance!

[JohnTopley]

Try

=AVERAGEIF(A:A,A2,B:B)

[major_johnson]

Yes, works like a charm!

Is there a way to create a unique names column thereby simply dragging the formula over it to create results?

This is to suppose if some more names are entered into the original column.

I hope I am not creating confusion.

[JohnTopley]

In D2

=IFERROR(INDEX($A$2:$A$367, MATCH(0, COUNTIF($D$1:$D1, $A$2:$A$367))),"")

Enter with ctrl+shift+Enter

[major_johnson]

Yes, perfection!

I am sorry I came back after this long to reply.

[JohnTopley]

You're welcome. No apology necessary!