How can I assign a value to names in Excel and then find the difference between the values?-CodePudding

I have 18 students in a competition. I have their names listed in the first column. I have another column that has ratings. The ratings column won't change, but the names can move up or down depending on performance. If I wanted to compare two students, how can I type two names into cells to find the difference in value?

Ex.

Joe   63    
Bill  59    
Sarah 45    
Deb   37    
Jill  22    
Tom   10

If I want to compare Jill to Sarah, I'll type Jill in one cell, type Sarah in the next cell and get the value 23. Also for any value that is 10 or less, I want it to equal 0. For example, Deb and Sarah are only 8 apart, which isn't significant, so it equals 0.

Thank you for your help.

CodePudding user response：

Since we do not know which of the names will be the larger value and you are just looking for the difference, it will be important to use an absolute value comparison. For this solution I used data validation for the cells F1 and F2 to simplify the name selection to drop down boxes instead of actually typing in the names. Note there may be some issues if there are duplicate names in the list.

For the formula to get the difference use the following and update the references to suit your data:

=IF(ABS(INDEX(B1:B6,MATCH($F$1,$A$1:$A$6,0))-INDEX(B1:B6,MATCH($F$2,$A$1:$A$6,0)))<=10,0,ABS(INDEX(B1:B6,MATCH($F$1,$A$1:$A$6,0))-INDEX(B1:B6,MATCH($F$2,$A$1:$A$6,0))))