Search notes:

Excel function: match

=match(value, array)
=match(value, array, matchType)
match returns the (1-based) position of the element in array that is equal to or comes closest to the value of value.
If matchType is 0, the value in array must be equal to the value searched for and the order of the elements in array is not important.
If matchtype is 1, the array must be sorted in ascending order and match(…) returns the last position of the element whose value is equal or smaller than the value searched for.
If matchtype is -1, the opposite is true: the array must be sorted in descending order and match(…) returns the position of the last element in array whose value is larger or equal to the value searched for.
The default for matchtype is 1.

Hardcoded value for 2nd parameter

It's possible to use hardcoded values in the 2nd parameter using curly braces.
If the value of cell d3 is mi, the following example evaluates to 3. 
=match(d3; {"mo","di","mi", "do", "fr", "sa", "so"}; 0)

Demonstration

The following example tries to demonstrate the different possible combinations.
The values in the first column are ordered and a named Range is assigned to it: someNumbers. Such a named range can be used to refer to array in `match(…, arrayName, …).
The values in the second column are unordered and the name of its range is spellings.
The fourth and seventh column show a function that uses match() and the cells right to it what the formula evaluates to.
It can be seen that array needs to be ordered when using matchtype different from 0. For example, match("thirty", spellings, 1) returns 2.
This example was created with the following Visual Basic for Application code: 
option explicit

dim dataRow    as integer
dim formulaRow as integer
dim formulaCol as integer

sub main() ' {

    fillData

    range(cells(1, 1), cells(dataRow, 1)).name = "someNumbers"
    range(cells(1, 2), cells(dataRow, 2)).name = "spellings"

    formulaCol = 4
  '
  ' 39 is a number that exists in someNumbers
  '
    addFormula "match(39, someNumbers)"
    addFormula "match(39, someNumbers, 0)"
    addFormula "match(39, someNumbers, 1)"

  '
  ' 42 is a number that does not exist in someNumbers
  '
    addFormula "match(42, someNumbers)"
    addFormula "match(42, someNumbers, 0)"
    addFormula "match(42, someNumbers, 1)"


    formulaRow = 0
    formulaCol = 7
  '
  ' fifty-three is a string that exists in spellings
  '
    addFormula "match(""fifty-three"", spellings)"
    addFormula "match(""fifty-three"", spellings, 0)"
    addFormula "match(""fifty-three"", spellings, 1)"

  '
  ' thirty is a string that does not exist in spellings
  '
    addFormula "match(""thirty"", spellings)"
    addFormula "match(""thirty"", spellings, 0)"
    addFormula "match(""thirty"", spellings, 1)"

    range(cells(1,1), cells(1,8)).entireColumn.autoFit

    cells(dataRow+2, 10).select

end sub ' }

sub fillData() ' {

    addDataRow 12, "twelve"
    addDataRow 17, "seventeen"
    addDataRow 21, "twenty-one"
    addDataRow 25, "twenty-five"
    addDataRow 31, "thirty-one"
    addDataRow 39, "thirty-nine"
    addDataRow 53, "fifty-three"
    addDataRow 74, "seventy-four"
    addDataRow 99, "ninety-ine"

end sub ' }

sub addDataRow(value as integer, text as string) ' {

    dataRow = dataRow + 1
    cells(dataRow, 1) = value
    cells(dataRow, 2) = text

end sub ' }

sub addFormula(text as string) ' {

    formulaRow = formulaRow + 1
    cells(formulaRow, formulaCol  )             =       text
    cells(formulaRow, formulaCol+1).formulaR1C1 = "=" & text
    cells(formulaRow, formulaCol  ).font.name   = "Courier New"

end sub ' }
Github repository about-Excel, path: /functions/index-match-lookup-etc/match.bas

Index