Can MATCH perform a case-insensitive lookup?

MATCH itself is case-insensitive in both Excel and Google Sheets; it treats "Bob" and "bob" as the same when match_type is 0. If you need a case-sensitive search, wrap the lookup_value and lookup_array in EXACT or use an array formula with FILTER.

What does MATCH return when the lookup_array contains duplicate values?

MATCH returns the position of the first occurrence it encounters when scanning from the top (or left) of the range. Duplicate entries later in the range are ignored for that particular call.

How do wildcards work with MATCH?

When match_type is set to 0, you can include the asterisk (*) to match any sequence of characters and the question mark (?) to match a single character. For example, MATCH("TCK-10*",A2:A6,0) would locate the first ticket ID that starts with "TCK-10".

Why does MATCH sometimes return a number that seems off by one?

MATCH reports the position relative to the first cell of the lookup_array, not the absolute worksheet row or column number. If your range starts at row 2, a returned value of 3 actually corresponds to worksheet row 4.

MATCH function

MATCH returns the relative position of a lookup value within a one-dimensional range, optionally using exact, less-than, or greater-than matching.

=MATCH(lookup_value, lookup_array, [match_type])

Generate a MATCH formula

Describe what you need. The generator will reach for MATCH where MATCH is the right tool, and tell you when it is not.

Arguments

How MATCH reads its arguments

Argument	Required	Description
lookup_value	Required	The value to search for; can be a number, text, or logical value, and if text contains * or ?, they act as wildcards when match_type is 0.
lookup_array	Required	A single row or column range that contains the data to be searched; if the range has more than one dimension MATCH returns a #VALUE! error.
match_type	Optional	Optional flag –1, 0, or 1 that controls the search mode: -1 finds the smallest value greater than or equal, 0 finds an exact match (wildcards allowed), and 1 finds the largest value less than or equal; omitted defaults to 1.

Returns

MATCH returns a single numeric position as a scalar.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Locate a ticket ID in the export

Ticket ID	Priority	Opened	Closed	Agent	CSAT
TCK-1001	High	2023-07-01	2023-07-02	Alice	5
TCK-1002	Medium	2023-07-03	2023-07-04	Bob	4
TCK-1003	Low	2023-07-05	2023-07-06	Carol	3
TCK-1004	High	2023-07-07	2023-07-08	Dave	5
TCK-1005	Medium	2023-07-09	2023-07-10	Eve	2

=MATCH("TCK-1003",A2:A6,0)

Result: 3

The formula searches the Ticket ID column (A2:A6) for the exact string "TCK-1003". Because MATCH is set to 0 (exact match), it scans down the range and finds the value in the third cell of the range, so it returns 3. This number represents the relative position within the specified lookup_array, not the absolute worksheet row number.

2. Find the position of a priority level

Ticket ID	Priority	Opened	Closed	Agent	CSAT
TCK-1001	High	2023-07-01	2023-07-02	Alice	5
TCK-1002	Medium	2023-07-03	2023-07-04	Bob	4
TCK-1003	Low	2023-07-05	2023-07-06	Carol	3
TCK-1004	High	2023-07-07	2023-07-08	Dave	5
TCK-1005	Medium	2023-07-09	2023-07-10	Eve	2

=MATCH("Low",B2:B6,0)

Result: 3

Here the lookup_array is the Priority column (B2:B6). MATCH looks for the exact word "Low". The word appears in the third row of the selected range, so the function returns 3. If the priority text were misspelled or missing, MATCH would produce #N/A.

3. Determine an agent's alphabetical rank

Ticket ID	Priority	Opened	Closed	Agent	CSAT
TCK-1001	High	2023-07-01	2023-07-02	Alice	5
TCK-1002	Medium	2023-07-03	2023-07-04	Bob	4
TCK-1003	Low	2023-07-05	2023-07-06	Carol	3
TCK-1004	High	2023-07-07	2023-07-08	Dave	5
TCK-1005	Medium	2023-07-09	2023-07-10	Eve	2

=MATCH("Bob",E2:E6,-1)

Result: 2

With match_type -1, MATCH searches for the smallest value that is greater than or equal to the lookup_value when the data are sorted in descending order. The Agent column is already in alphabetical order (Alice, Bob, Carol, Dave, Eve). "Bob" is the second entry, so MATCH returns 2. If the column were not sorted descending, MATCH would still return the first exact match because -1 also works for exact matches when the value exists.

Common errors

Which MATCH error are you seeing?

Error	Why it happens	How to fix it
#N/A	The lookup_value does not exist anywhere in the lookup_array, so MATCH cannot find a position.	Confirm the value you are searching for is present, or wrap the MATCH call in IFERROR to supply an alternative result.
#VALUE!	The match_type argument is something other than -1, 0, or 1, or the lookup_array contains more than one column or row.	Use only -1, 0, or 1 for match_type and ensure the lookup_array is a single row or column range.
#REF!	The lookup_array references a range that has been deleted or is otherwise invalid, such as a column that no longer exists.	Adjust the reference so it points to an existing, correctly sized range.

Tips and when to use something else

Combine MATCH with INDEX to retrieve a value from a different column once you know the row position.
If you need to return both the row and column of a match in a two-dimensional table, consider using XLOOKUP or the newer XMATCH function.
Always sort the lookup_array appropriately when using match_type 1 or -1; otherwise MATCH may return an unexpected position.
When you need fuzzy or wildcard matching beyond the simple * and ?, switch to XMATCH with match_mode 2 for a more powerful search.

Related functions

VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.XMATCHXMATCH returns the relative position of a lookup value within a one-dimensional range, supporting exact, wildcard and next-larger/smaller matches.OFFSETOFFSET returns a reference to a range that is a specified number of rows and columns from a starting cell, optionally sized by height and width.

Frequently asked questions

Can MATCH perform a case-insensitive lookup?: MATCH itself is case-insensitive in both Excel and Google Sheets; it treats "Bob" and "bob" as the same when match_type is 0. If you need a case-sensitive search, wrap the lookup_value and lookup_array in EXACT or use an array formula with FILTER.
What does MATCH return when the lookup_array contains duplicate values?: MATCH returns the position of the first occurrence it encounters when scanning from the top (or left) of the range. Duplicate entries later in the range are ignored for that particular call.
How do wildcards work with MATCH?: When match_type is set to 0, you can include the asterisk (*) to match any sequence of characters and the question mark (?) to match a single character. For example, MATCH("TCK-10*",A2:A6,0) would locate the first ticket ID that starts with "TCK-10".
Why does MATCH sometimes return a number that seems off by one?: MATCH reports the position relative to the first cell of the lookup_array, not the absolute worksheet row or column number. If your range starts at row 2, a returned value of 3 actually corresponds to worksheet row 4.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17