MATCH function in Google Sheets: find the position of a value
MATCH returns the position of a value in a single row or column: 4 if it is the fourth item of the range. On its own it answers where something is and whether it exists; with INDEX, VLOOKUP and other functions it makes lookups that do not depend on fixed column numbers. This guide covers the syntax, the three search types, wildcards, dates, typical combinations and errors.
MATCH returns the position of a value in a row or a column:
=MATCH("Google", C3:C17, 0)
The formula looks for “Google” in C3:C17 and returns 2 if it is the second cell of the range. The position is counted from the start of the range, not from row 1 of the sheet. The 0 at the end asks for an exact match, and in most formulas it should be there.
MATCH syntax
=MATCH(search_key, range, [search_type])
- search_key: the value to find: a text in quotes, a number, a date or a cell reference.
- range: one row or one column. With a range of several rows and columns, MATCH returned #N/A in our test.
- search_type: optional, 1 by default.
| search_type | Finds | The range must be |
|---|---|---|
| 0 | The first exact match | In any order |
| 1 (default) | The largest value less than or equal to the key | Sorted in ascending order |
| -1 | The smallest value greater than or equal to the key | Sorted in descending order |
The default is the trap. In our test =MATCH("Sarah", C3:C12), without the third argument, returned 10 on an unsorted column where the first “Sarah” is in position 2. No error, just a wrong position. Write the 0 unless you mean an approximate match.
What an exact match accepts
All from our test sheet:
- Case is ignored. “mike” finds “Mike”. For a case-sensitive match, see EXACT.
- Wildcards work.
"Sa*"matched “Sarah” and"M?ke"matched “Mike”: the asterisk stands for any characters, the question mark for one. - The first match wins. With several equal values, MATCH returns the position of the first.
- Numbers and texts are different. The text “3200” did not match the number 3200, and the text “3/15/2023” did not match a date.
Exact match examples
A text
=MATCH("Live Broadcast", B3:B17, 0)
A value from a cell
=MATCH(D3, B3:B17, 0)
A row instead of a column
=MATCH("Banner", B2:E2, 0)
A date
Give the date as a date, with DATE or from a cell, not as a text:
=MATCH(DATE(2024, 5, 21), B3:B17, 0)
The position of the largest value
=MATCH(MAX(D3:D12), D3:D12, 0)
OWOX Data Marts
See your first report built in real time. 15 minutes.
- Connect your data warehouse
- Pick your metrics
- Get a live Google Sheets report
In the time it takes to write a ticket. Then imagine never writing that ticket again.
Book a DemoWe'll use your actual use case
Approximate match
With search type 1 and a range sorted in ascending order, MATCH returns the position of the largest value that does not exceed the key:
=MATCH(200, B3:B17, 1)
200 is not in the list; 140 is the largest value below it, in position 2. This is how brackets work: tax bands, discount tiers, grades. With -1 and a range sorted in descending order, the result is the smallest value that is at least the key.
On an unsorted range, an approximate match returns a position that means nothing: in our test, 2400 against ten unsorted amounts gave 9, and 6 once the same amounts were sorted.
INDEX and MATCH
MATCH finds the row, INDEX returns the value from it:
=INDEX(C:C, MATCH("C102", B:B, 0))
The pair does what VLOOKUP does, without its limits: the result column can be to the left of the key column, and inserting a column does not break the formula. With a second MATCH for the column, the lookup becomes two-way:
=INDEX(B3:D12, MATCH(4500, D3:D12, 0), MATCH("Sales rep", B2:D2, 0))
The first MATCH finds the row of the amount 4500, the second the column with the header “Sales rep”. The full guide is INDEX and MATCH.
MATCH inside other functions
A column number for VLOOKUP
A VLOOKUP index typed as a number points to the wrong column as soon as one is inserted. MATCH on the header row finds the column by its name:
=VLOOKUP(G2, B3:D, MATCH(G3, B2:D2, 0), FALSE)
Changing the header name in G3 switches the column that is returned. See VLOOKUP.
Is the value in the list
MATCH returns #N/A when the key is missing, which makes it a membership test:
=ISNUMBER(MATCH("Mike", C3:C12, 0))
returns TRUE if “Mike” is in the range. To show a text instead of the error:
=IFERROR(MATCH("Poll", C2:C17, 0), "Not found")
IFNA is the narrower choice: it replaces only #N/A and lets other errors show.
The first row that meets a condition
=ARRAYFORMULA(MATCH(TRUE, D3:D12 > 3000, 0))
The comparison produces a column of TRUE and FALSE, and MATCH finds the first TRUE: in our test, position 4.
A custom sort order
MATCH(value, {"High";"Medium";"Low"}, 0) turns a label into its place in your own order, which SORT can then sort by.
XMATCH: the newer version
XMATCH has the same job with different defaults and more options: it matches exactly unless told otherwise and can search from the end. In our test =XMATCH("Sarah", C3:C12, 0, -1) returned 10, the position of the last “Sarah”, where MATCH returned 2, the first. Its lookup counterpart is XLOOKUP.
Errors in MATCH
⚠️ Error: #N/A
✅ Solution: The key is not in the range. Check for a trailing space, for a number stored as text on one side, and for a date typed as a text. The error also appears when the range has more than one row and more than one column.
⚠️ Error: A position that is clearly wrong, with no error.
✅ Solution: The third argument is missing or 1, and the range is not sorted. Add 0.
⚠️ Error: The position is off by one or more.
✅ Solution: The position is relative to the range. If the range starts in row 3, position 1 is row 3. Give INDEX the same starting row as MATCH.
⚠️ Error: A key with an asterisk or a question mark matches the wrong row.
✅ Solution: They are wildcards. To search for the characters themselves, put a tilde in front: "~*".
Related Google Sheets functions
Related Google Sheets functions
- INDEX and MATCH: The lookup pair in detail.
- VLOOKUP: Lookup by the first column.
- XLOOKUP and VLOOKUP: The newer lookup function.
- HLOOKUP: Lookup along a row.
- EXACT: Case-sensitive matching.
- RANK: The position of a value by size.
A position is only right while the layout holds
A position is only right while the layout holds
MATCH answers where a value sits in the range today. Reports built on pasted exports change their layout with every paste: a new column, a different order of rows, a header renamed by the source. Formulas with MATCH on the header survive some of this and fail on the rest with #N/A.
The OWOX Data Marts extension for Google Sheets covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas.
What changes for the person who maintains the sheet:
- Refresh replaces re-export. Refresh current report in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it.
- The source is written on the sheet. The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report.
- No SQL and no warehouse access needed. The analyst decides which data marts and columns are available. You choose from that list.
The extension does not write or change the analyst’s SQL. The columns of the report are then the columns of the query, the same on every refresh, and MATCH has a stable header row to work with. The extension comes with the OWOX Cloud plans of OWOX Data Marts; spreadsheet reporting describes both ways data reaches a sheet.
Turn your data into decisions.
Governed data marts give you the clean foundation ML needs to actually work.
- No AI hallucinations
- Analyst-governed definitions
- Every number traces to SQL
Frequently Asked Questions
How Do I Troubleshoot Common Errors with the MATCH Function?
#N/A means the key was not found: check for trailing spaces, numbers stored as text, and dates typed as text. #N/A also appears when the range has more than one row and more than one column. A wrong position without any error usually means the third argument is missing, so MATCH ran an approximate match on unsorted data; add 0 for an exact match.
What Are Some Best Practices for Using MATCH Function in Complex Sheets?
Write the third argument explicitly, 0 for an exact match. Keep the range to one row or one column. Give INDEX and MATCH ranges that start in the same row. Use MATCH on the header row instead of typed column numbers, and wrap lookups in IFNA to handle missing keys.
How Can I Use the MATCH Function to Search for a Date?
Give the date as a real date, not as text: =MATCH(DATE(2024, 5, 21), B3:B17, 0), or refer to a cell that holds the date. A date typed in quotes is a text and returns #N/A against a column of dates.
What is the MATCH Function in Google Sheets, and How Does It Work?
The MATCH function in Google Sheets searches for a specified value within a range and returns its relative position. It compares the search value with each value in the range and returns the position of the first occurrence.
Can You Combine the MATCH Function with INDEX for More Complex Lookups?
Yes, you can combine the MATCH function with INDEX to perform more complex lookups. MATCH is used to find the position, and INDEX is used to retrieve the value at that position.
Can the MATCH Function be Used to Perform Searches in a Horizontal Range?
Yes, the MATCH function can be used to search in a horizontal range by specifying the range horizontally instead of vertically in the formula.
