Finding an average of two columns with some same, some different identifiers, vlookup? ifavg?

[DCDireWolf]

I have a list of names in column A, a list of their numeric scores in Column B. Then I have a list of names in Column C, some of these names are the same as in Column A, some are different, and a list of their numeric scores in Column D.

I'd like to return the average score (the average of the values in columns B and D that match that name) of each person using formulas into a new two new columns (F and G), one with the name, one with the average.

See example below, assume cell A1 contains "Name 1". Do I use IFAVG? VLOOKUP? Both? Something else? Help!!

A B C D E F G
[TABLE="width: 482"]
<colgroup><col span="6"><col></colgroup><tbody>[TR]
[TD]Name 1[/TD]
[TD]Score[/TD]
[TD]Name 2[/TD]
[TD]Score[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Average Score[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]12[/TD]
[TD]Roger[/TD]
[TD]24[/TD]
[TD][/TD]
[TD]John[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]4[/TD]
[TD]John[/TD]
[TD]55[/TD]
[TD][/TD]
[TD]Jill[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]****[/TD]
[TD]3[/TD]
[TD]Jill[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]Roger[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]6[/TD]
[TD]Lazlow[/TD]
[TD]99[/TD]
[TD][/TD]
[TD]****[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alice[/TD]
[TD]1[/TD]
[TD]Bethany[/TD]
[TD]122[/TD]
[TD][/TD]
[TD]Lazlow[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]2[/TD]
[TD]Bill[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Alice[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Frank[/TD]
[TD]9[/TD]
[TD]Frank[/TD]
[TD]54[/TD]
[TD][/TD]
[TD]Bethany[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lisa[/TD]
[TD]11[/TD]
[TD]Lisa[/TD]
[TD]56[/TD]
[TD][/TD]
[TD]Bill[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Darlene[/TD]
[TD]12[/TD]
[TD]Annette[/TD]
[TD]98[/TD]
[TD][/TD]
[TD]Frank[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Lisa[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Darlene[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Annette[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Harry[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DCDireWolf]

Ha. Those **** are because the site thinks a common nickname for Richard is a swear word.

 

[sandy666]

If you are allowed to use PowerQuery you can try
- load source table to PQ editor
- duplicate table
- remove name2 and score2 from the first, change name1 to name
- remove name1 and score from the second table, change name2 and score2 to name and score
- append these tables
then try this

let
    Source = Table.Combine({Table1, #"Table1 (2)"}),
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Avg", each List.Average([Score]), type number}, {"Sum", each List.Sum([Score]), type number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Name", Order.Ascending}})
in
    #"Sorted Rows"

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Name[/td][td]Avg[/td][td]Sum[/td][/tr]

[tr=bgcolor:#FFFFFF][td]****[/td][td]

3​

[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Alice[/td][td]

1​

[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Annette[/td][td]

98​

[/td][td]

98​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Bethany[/td][td]

122​

[/td][td]

122​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Bill[/td][td]

2.5​

[/td][td]

5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Darlene[/td][td]

12​

[/td][td]

12​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Frank[/td][td]

31.5​

[/td][td]

63​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Harry[/td][td]

6​

[/td][td]

6​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Jill[/td][td]

4.5​

[/td][td]

9​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]John[/td][td]

33.5​

[/td][td]

67​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Lazlow[/td][td]

99​

[/td][td]

99​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Lisa[/td][td]

33.5​

[/td][td]

67​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Roger[/td][td]

24​

[/td][td]

24​

[/td][/tr]
[/table]

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Name 1[/TD]
[TD]Score[/TD]
[TD]Name 2[/TD]
[TD]Score[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Average Score[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]John[/TD]
[TD]

12​

[/TD]
[TD]Roger[/TD]
[TD]

24​

[/TD]
[TD][/TD]
[TD]John[/TD]
[TD]

33.5​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Jill[/TD]
[TD]

4​

[/TD]
[TD]John[/TD]
[TD]

55​

[/TD]
[TD][/TD]
[TD]Jill[/TD]
[TD]

4.5​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]****[/TD]
[TD]

3​

[/TD]
[TD]Jill[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD]Roger[/TD]
[TD]

24​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Harry[/TD]
[TD]

6​

[/TD]
[TD]Lazlow[/TD]
[TD]

99​

[/TD]
[TD][/TD]
[TD]****[/TD]
[TD]

32​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Alice[/TD]
[TD]

1​

[/TD]
[TD]Bethany[/TD]
[TD]

122​

[/TD]
[TD][/TD]
[TD]Lazlow[/TD]
[TD]

99​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Bill[/TD]
[TD]

2​

[/TD]
[TD]Bill[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]Alice[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Frank[/TD]
[TD]

9​

[/TD]
[TD]Frank[/TD]
[TD]

54​

[/TD]
[TD][/TD]
[TD]Bethany[/TD]
[TD]

122​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]Lisa[/TD]
[TD]

11​

[/TD]
[TD]Lisa[/TD]
[TD]

56​

[/TD]
[TD][/TD]
[TD]Bill[/TD]
[TD]

2.5​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]Darlene[/TD]
[TD]

12​

[/TD]
[TD]Annette[/TD]
[TD]

98​

[/TD]
[TD][/TD]
[TD]Frank[/TD]
[TD]

31.5​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Lisa[/TD]
[TD]

33.5​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Darlene[/TD]
[TD]

12​

[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Annette[/TD]
[TD]

98​

[/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Harry[/TD]
[TD]

6​

[/TD]
[/TR]
</tbody>[/TABLE]



G2=AVERAGEIF($A$2:$D$10,$F2,$B$2:$D$10) copy down

 

[DCDireWolf]

MARZIOTULLIO, worked! Thanks!

 

[MARZIOTULLIO]

Hi

You are welcome

Try Rick