VBA - Max Values and Match

[adamscourfield]

Hi all,

I am stuck on a problem. I have need to insert a vba macro into a sheet for my admin team. I want to find the highest value from a range in one sheet and return the value to another sheet if the adjacent column matches. I also want it to return it to the next blank cell in that row. So...the highest score for Name 1 in sheet 2 would go into the next black row in column B for that name. Any help would be greatly appreciated.

Sheet 1
A B
Name 1. SCORE
Name 2. SCORE
Name 3. SCORE
Name 4. SCORE

Sheet 2
A B
Name 1. 1
Name 1. 10
Name 1. 8
Name 2. 7
Name 2. 6
Name 3. 5
Name 3. 4

 

[DanteAmor]

It can be with the following array formula.

varios 09jul2020.xlsm
	A	B
1	NAME	SCORE
2	Name 1	10
3	Name 2	7
4	Name 3	5
5	Name 4	0
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=MAX((Sheet2!$A$2:$A$8=A2)*(Sheet2!$B$2:$B$8))
Press CTRL+SHIFT+ENTER to enter array formulas.