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

Name 1	Score	Name 2	Score		Name	Average Score
John	12	Roger	24		John
Jill	4	John	55		Jill
****	3	Jill	5		Roger
Harry	6	Lazlow	99		****
Alice	1	Bethany	122		Lazlow
Bill	2	Bill	3		Alice
Frank	9	Frank	54		Bethany
Lisa	11	Lisa	56		Bill
Darlene	12	Annette	98		Frank
					Lisa
					Darlene
					Annette
					Harry

<colgroup><col span="6"><col></colgroup><tbody>
</tbody>

 

[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"

Name	Avg	Sum
****	3​	3​
Alice	1​	1​
Annette	98​	98​
Bethany	122​	122​
Bill	2.5​	5​
Darlene	12​	12​
Frank	31.5​	63​
Harry	6​	6​
Jill	4.5​	9​
John	33.5​	67​
Lazlow	99​	99​
Lisa	33.5​	67​
Roger	24​	24​

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​	F​	G​
1​	Name 1	Score	Name 2	Score		Name	Average Score
2​	John	12​	Roger	24​		John	33.5​
3​	Jill	4​	John	55​		Jill	4.5​
4​	****	3​	Jill	5​		Roger	24​
5​	Harry	6​	Lazlow	99​		****	32​
6​	Alice	1​	Bethany	122​		Lazlow	99​
7​	Bill	2​	Bill	3​		Alice	1​
8​	Frank	9​	Frank	54​		Bethany	122​
9​	Lisa	11​	Lisa	56​		Bill	2.5​
10​	Darlene	12​	Annette	98​		Frank	31.5​
11​						Lisa	33.5​
12​						Darlene	12​
13​						Annette	98​
14​						Harry	6​

<tbody>
</tbody>

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

 

[DCDireWolf]

MARZIOTULLIO, worked! Thanks!

 

[MARZIOTULLIO]

Hi

You are welcome

Try Rick