Excel function: match

match(value; array; match_type) returns the index of value in array.

match_type

match_type is optional and needs to be set to one of the following values:

`-1`	less than match
`0`	exact match
`1`	greater than match

The default for match_type is 1.

Simple example

In the following simple example, =index("Baz", a1;a5) returns 3 because Bas is the third element in the range a1:a5.

option explicit

sub main() ' {

    testdata

    cells(7, 1).formula = "=match(""Baz"", a1:a5)"

end sub ' }

sub testdata() ' {

  '
  ' Clear testdata from previous run
  '
    activeSheet.cells.clearContents

    cells(1, 1) = "Foo"
    cells(2, 1) = "Bar"
    cells(3, 1) = "Baz"
    cells(4, 1) = "The other"
    cells(5, 1) = "The same"

end sub ' }

Github repository about-Excel, path: /functions/match/simple.bas

A more elaborate version is here.

Excel function: match

match_type

Simple example

See also