MATCHEXACT

Description

Looks up an exact match for the value in the sorted list (one-dimensional array) you specify, and returns the position of the value. You can use this function to match logical values, numeric values, or text strings. This function is similar to MATCH, but MATCHEXACT:

Always searches for an exact match; it returns an #N/A error if it doesn't find the value.

Doesn't allow wildcard characters such as * or ?.

Uses a binary search for better performance.

Syntax

MATCHEXACT(

lookup_value

,

lookup_array

, [

match_type

])

lookup_value

: The value to find.

lookup_array

: The array to search.

match_type

: The criteria to use when searching. Possible values are:

0 or greater = do a binary search for the specified

lookup_value

.

lookup_array

data must be sorted in ascending order. If you omit the argument, the function uses this as the argument value.

Less than 0 = do a binary search for the specified

lookup_value

.

lookup_array

data must be sorted 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	Organization 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	2	SalesCC-4 SalesGMS-UKLondon
6	Sales	CC-4 Sales	GMS-UK	Toronto	0	0	1	SalesCC-4 SalesGMS-UKToronto
7					5	7	9

For the formula

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

, the result is

3

.

You can also return the matched value instead of its position by combining the MATCHEXACT and INDEX functions.

For the formula

=INDEX(G2:G6,MATCHEXACT("CC-3 Development",B2:B6,1))

:

First, MATCHEXACT searches the array B2:B6 for the string "CC-3 Development" and returns the position of the value, which is

3

. Then, INDEX uses

3

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

2

.

Notes

If multiple matches exist, the function returns the position of the first match.

Related Functions

MATCH

MHLOOKUP

MVLOOKUP