Column index finder
=MATCH("Revenue", B1:Z1, 0) returns column offset for a dynamic INDEX pull.
What is MATCH in Google Sheets?
MATCH searches a range for a value and returns its relative position. Feeding INDEX or OFFSET with MATCH replaces brittle column index math. Sorted approximate match modes support tier tables and date brackets.
When to use it
Use MATCH inside INDEX for lookups, to find column numbers for dynamic references, or to locate ranks in sorted lists.
When to skip it
Skip MATCH alone when XLOOKUP returns the value directly. Use MATCH when you need position, not value.
How it works
-
1
Pass lookup value and one-row or one-column search range.
-
2
Set match_type 0 for exact match, 1 or -1 for sorted approximate.
-
3
Wrap with INDEX to return the corresponding value from another column.
-
4
Sort data when using approximate match modes as documented.
-
5
Handle #N/A with IFERROR when keys may be missing.
-
6
Use COUNTIF first if duplicate keys make first-match behavior risky.
Examples in Google Sheets
INDEX MATCH row
=INDEX(B:B, MATCH(D2, A:A, 0)) classic vertical lookup pair.
Bracket tier
=MATCH(E2, Tiers!A:A, 1) on ascending sorted tiers finds bracket position for tax logic.
Build this without starting from a blank cell
Use a Better Sheets tool for MATCH, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Approximate match on unsorted data returning wrong positions.
- Duplicate keys returning only first match silently.
- Horizontal vs vertical range mismatch with lookup value orientation.
- Text number mismatch when keys look equal but types differ.
- Whole-column MATCH on massive sheets slowing every recalc.
Frequently asked questions
- MATCH vs XLOOKUP?
- MATCH returns position. XLOOKUP returns the matched value directly.
- MATCH exact mode?
- Use 0 as third argument for exact match regardless of sort order.
- MATCH wildcards?
- Approximate modes may support wildcards in some contexts. Exact 0 does not.
- Why #N/A?
- Value not found in range with exact match.
- MATCH on dates?
- Works when dates are true serial values, not text.
- Multiple matches?
- MATCH returns first match only. FILTER for all.
- MATCH with INDEX?
- Classic flexible lookup pair predating XLOOKUP.
Related Tutorials
Watch how MATCH works
REGEXMATCH Formula Generator for Google Sheets
How to Find Duplicates
200 REGEXMATCH Examples in Google Sheets
Use Index Match when you want Vlookup
How do I reference a different spreadsheet in Apps Script?
Search Every Function in Google Sheets
Related blog posts
Guides that explain MATCH in more depth.
Five Frustrating Formulas and How to Conquer Them
Google Sheets formulas can be frustrating for a few reasons. First, you’re trying to remember what to use on the fly. Then you can't remember the name of a formula. If you do figure out the name, you can't remember the syntax. Then you don't know the proper way to write the data. FRUSTRATING!
Read post →Name Match: Who's In and Who's Out
Let's say you have a master list with over 700 names and a new list from a recent meeting containing 175 names. How will you identify which names from the new list aren't on the master list so they can be added.
Read post →Philippines Phone Number Validation: Use of REGEXMATCH in Google Sheets
Every school year thousands of students and teachers’ data were being added to the school database. As someone who was always given the task to narrow down lots of information after the school enrollment, validating students’ data manually is very exhausting. Take this as an example, when you ask students to include the phone numbers of their emergency contact person, there are some instances wherein the declared numbers were invalid due to human error. And the assigned personnel during enr...
Read post →Related glossary terms
INDEX
INDEX fetches a value from a range by row and column number. Combined with MATCH it powers flexible lookups. Alone it picks nth items from lists, dynamic headers, or matrix intersections without VLOOKUP column limits.
Read guide →OFFSET
OFFSET starts from a reference cell and returns a range moved by a row and column offset with optional height and width. Rolling averages, dynamic windows, and shifted blocks use OFFSET, though INDEX and FILTER are often clearer today.
Read guide →XLOOKUP
XLOOKUP searches a lookup array for a key and returns a matching value from a return array. It handles left lookups, approximate match, and custom not-found text without INDEX/MATCH gymnastics.
Read guide →VLOOKUP
VLOOKUP searches the first column of a range for a key and returns a value from a specified column to the right. It is still everywhere in legacy templates even as XLOOKUP offers more flexible replacements.
Read guide →Done reading about MATCH?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.