Question

Hi, I need help with excel. I need to find the highest and lowest score from different soccer matches. For example;

match 1 = (2 - 0)

match 2 = (3 - 1)

match 3 = (0 - 1)

match 4 = (2 - 3)

How can I use index, max, min and match functions to automatic find the highest and lowest scores ? for example, match 4 = highest, match 3 = lowest.

Excel edition 2016

There is basically no inputs in this one. I just want to know how to type in the right functions to get the answer.

Answer 1

INDEX function (To use in VBA, we write WorksheetFunction.Index() ):

This is used to find a particular value in a range or array, given the range or array, and the row number.

Syntax:

INDEX (array/range, row_num, [col_num])

*column number is optional*

MATCH function (To use in VBA, we write WorksheetFunction.Match()):

This returns the index of a particular value in a given range/array.

Syntax:

MATCH(value, array/range, [match_type])

*match_type = 0 for exact match, 1 for approximate/nearest match*

MAX function (To use in VBA, we write WorksheetFunction.Max()):

This returns maximum value in a array/range.

Syntax:

MAX(array/range)

*MIN function is similar, but different than MAX as it returns minimum in a array/range*

Steps for Solution: (Refer to VBA code also)

Fill the excel sheet like shown in figure:

B C D team A team B Score 1 3 match 1 4 match 2 5 match 3 6 match 4

Step1: Calculate the sum of points of both teams in each match, to get the score.Store it in an array, and fill these values in the Score column.

Step2: Find the maximum and minimum of these scores, using Max and Min function.

Step3: By using Match function, get the index/row number of maximum and minimum scores.

Step4: By using Index function, get the corresponding match numbers of highest and lowest scores.

VBA Code(Explanation also in Comments):

Sub Score()

'array to store scores
Dim scoresum(1 To 4) As Integer
Dim i As Integer
Dim j As Integer
Dim maxscore As Integer
Dim minscore As Integer
Dim ind As Integer
Dim hmatch As String
Dim lmatch As String

'variable to iterate in scoresum array
j = 1

'For loop to calculate sum of points and store in scoresum array
'also fill these values in Score clumn in the sheet
For i = 3 To 6
scoresum(j) = Cells(i, 2).Value + Cells(i, 3).Value
Cells(i, 4).Value = scoresum(j)
j = j + 1
Next i

'find maximum score from the array
maxscore = WorksheetFunction.Max(scoresum)
'find index of maximum score in the score column
ind = WorksheetFunction.match(maxscore, Range("D3:D6"), 0)
'find the corresponding match number using index function
hmatch = WorksheetFunction.Index(Range("A3:A6"), ind)

'find minimum score from the array
minscore = WorksheetFunction.Min(scoresum)
'find index of minimum score in the score column
ind = WorksheetFunction.match(minscore, Range("D3:D6"), 0)
'find the corresponding match number using index function
lmatch = WorksheetFunction.Index(Range("A3:A6"), ind)

'Print in message box
MsgBox (hmatch + " = highest, " + lmatch + " = lowest")

End Sub
Output SS:

B C D E F G H team A team B Score Microsoft Excel 2 3 match 1 4 match 2 5 match 3 6 match 4 1 1 match 4 = highest, match 3 = lowest 0 1 | OK