MATCH

Description

Looks up a value in the specified list (one-dimensional array), and returns the position of the value. You can match numeric values, logical values, or text strings. With text strings you can match patterns: use an asterisk (*) to match any number of any characters; use a question mark (?) to match any single character.

Syntax

MATCH(

lookup_value

,

lookup_array

, [

match_type

])

lookup_value

: The value to find.

lookup_array

: The array to search.

match_type

: Specifies the criteria to use when searching.

1 or Empty = find the largest value less than or equal to

lookup_value

.

lookup_array

data must be in ascending order. The default is 1.

0 = Find an exact match to

lookup_value

. With text strings you can use the asterisk (*) and question mark (?) characters to match patterns.

-1 = find the smallest value greater than or equal to

lookup_value

.

lookup_array

data must be in descending order.

Example

The examples are based on this workbook:

	A	B	C	D	E	F	G	H
1	Supervisory Org	Cost Center	Company	Location	Jan (HC) 2017	Feb (HC) 2017	Mar (HC) 2017	PEA KEY
2	Finance	CC-1 Finance	GMS	Pleasanton	2	2	2	FinanceCC-1 FinanceGMSPleasanton
3	Product Management	CC-2 Product	GMS	Boulder	0	1	2	Product ManagementCC-2 ProductGMSBoulder
4	Development	CC-3 Development	GMS-CA	Toronto	2	2	2	DevelopmentCC-3 DevelopmentGMS-CAToronto
5	Sales	CC-4 Sales	GMS-UK	London	1	2	3	SalesCC-4 SalesGMS-UKLondon
6	Sales	CC-4 Sales	GMS-UK	Toronto	0	0	1	SalesCC-4 SalesGMS-UKToronto
7					5	7	9

The result of

=MATCH("CC-3 Development",B2:B6)

is

3

.

The result of

=MATCH("*Sales*",B2:B6,0)

is

4

.

You can combine the MATCH and INDEX functions to return the matched value instead of the position of the value.

For the formula:

=INDEX(G2:G6,MATCH("*Sales*",B2:B6,0))

First, MATCH searches the range B2:B6 for the string "*Sales*" (using a wildcard) and returns the position of the value, which is

4

. Then INDEX uses

4

as its second argument and returns the value in position 4 of G2:G6, which is

2

.

Notes

If there are multiple matches, the function returns the position of the first one.

Wildcard examples when finding text patterns:

Search for

*00

to find values that end with two zeros.

Search for

1?5

to find all three-digit values that begin with 1 and end with 5.

If the value to find contains a * or ?, precede it with a tilde (~). Example: search for

~*N/A~*

to find the text

*N/A*

.

We recommend using the MATCHEXACT function instead of MATCH where possible, for better performance.

Related Functions

MATCHEXACT

MHLOOKUP

MVLOOKUP