How to insert variables depending on sort function table

[KurtisT6]

Hello,

I am collecting data for my class each week during PE depending on certain variables. I have put each title ( accuracy, speed, passing) into seperate tables. For example in table 1 the accuracy column shows the accuracy for each student and since student A has the highest accuracy, they have the highest multiplier of 5. In table 2 I then want the value of the multiplier of each student to be automatically insterted depedning on the name and multiplier. The problem is I dont want to type in the multiplier I want it to be done automatically as there are 36 students in the class it will speed up the process each week. When I update the table 1 next week the multiplier of each student could change.

Table 1:

Multiplier	Name	Accuracy​
5	A	100​
4.9	B	90​
4.8	C	85​
4.7	D	80​
4.6	E	75​
4.5	F	68​
4.4	G	67​
4.3	H	65​
4.2	I	60​
4.1	J	57​
4	K	54​

Table 2:

Name	Accuracy	Passes	Speed
A	5
B	4.9

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Multiplier	Name	Accuracy			Name	Accuracy	Passes	Speed
2	5	A	100			A	5
3	4.9	B	90			B	4.9
4	4.8	C	85			J	4.1
5	4.7	D	80
6	4.6	E	75
7	4.5	F	68
8	4.4	G	67
9	4.3	H	65
10	4.2	I	60
11	4.1	J	57
12	4	K	54
Sheet4

Cell Formulas
Range	Formula
G2:G4	G2	=XLOOKUP(F2,$B$2:$B$12,$A$2:$A$12,"")