Google Sheets Formulas Cheat Sheet

Master Google Sheets with our comprehensive formulas reference guide. From basic math to powerful Google-exclusive functions like QUERY, ARRAYFORMULA, and IMPORTRANGE — find syntax, examples, and expert tips for every essential formula.

Found 19 formulas matching "MATCH"

XMATCH()

Lookup & Reference

Returns the position of a match with extended options

Syntax:

=XMATCH(search_key, lookup_array, [match_mode], [search_mode])

Examples:

=XMATCH("Apple", A1:A10)

Position of 'Apple' (exact match)

=XMATCH(100, A1:A10, 1)

Next larger value position

=XMATCH("z*", A1:A10, 2)

Wildcard match

Note: More powerful than MATCH; supports wildcards and binary search modes

REGEXMATCH()

Text

Tests whether text matches a regular expression

Syntax:

=REGEXMATCH(text, regular_expression)

Examples:

=REGEXMATCH(A1, "^[0-9]+$")

Check if A1 is all digits

=REGEXMATCH(A1, "@.+\.com")

Validate basic email pattern

=REGEXMATCH(A1, "(?i)yes")

Case-insensitive match for 'yes'

Note: Returns TRUE or FALSE; Google Sheets exclusive

MATCH()

Lookup & Reference

Searches for a value and returns its relative position in a range

Syntax:

=MATCH(search_key, range, [search_type])

Examples:

=MATCH("Apple", A1:A10, 0)

Find position of 'Apple' in A1:A10

=INDEX(B:B, MATCH(A1, A:A, 0))

INDEX-MATCH combo for lookup

=MATCH(MAX(A:A), A:A, 0)

Row of maximum value

Note: search_type 0=exact, 1=less than (sorted asc), -1=greater than (sorted desc)

XLOOKUP()

Lookup & Reference

Searches a range and returns a matching result with flexible options

Syntax:

=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

Examples:

=XLOOKUP(A1, B:B, C:C)

Look up A1 in B, return from C

=XLOOKUP(A1, B:B, C:C, "Not found")

Custom text if not found

=XLOOKUP(A1, B:B, C:C,, 0, -1)

Exact match, search from last

Note: Available in newer Sheets; supports reverse search and approximate matching

DSUM()

Database

Sums values in a column of a database that match specified criteria

Syntax:

=DSUM(database, field, criteria)

Examples:

=DSUM(A1:D100, "Sales", F1:F2)

Sum Sales column where F criteria match

=DSUM(A:D, 3, F1:F2)

Sum 3rd column matching criteria

Note: database includes headers; criteria range must also have matching headers

DGET()

Database

Extracts a single value from a column matching specified criteria

Syntax:

=DGET(database, field, criteria)

Examples:

=DGET(A1:D100, "Email", F1:F2)

Get Email for the single matching row

Note: Returns #NUM! if multiple rows match; returns #VALUE! if no rows match

REGEXREPLACE()

Text

Replaces text matching a regular expression with new text

Syntax:

=REGEXREPLACE(text, regular_expression, replacement)

Examples:

=REGEXREPLACE(A1, "[0-9]+", "#")

Replace numbers with #

=REGEXREPLACE(A1, "\s+", " ")

Collapse multiple spaces

=REGEXREPLACE(A1, "(\w+)", "[$1]")

Wrap each word in brackets

Note: Replaces all matches; Google Sheets exclusive

SWITCH()

Logical

Evaluates an expression against a list of cases and returns matching result

Syntax:

=SWITCH(expression, case1, value1, [case2, value2, ...], [default])

Examples:

=SWITCH(A1,1,"Jan",2,"Feb",3,"Mar","Other")

Map month number to name

=SWITCH(B1,"R","Red","G","Green","B","Blue")

Map code to color

Note: Cleaner alternative to nested IFS for exact-match cases

DAVERAGE()

Database

Averages values in a column that match specified criteria

Syntax:

=DAVERAGE(database, field, criteria)

Examples:

=DAVERAGE(A1:D100, "Score", F1:F2)

Average Score where criteria match

Note: Same structure as DSUM; criteria uses a separate range with headers

DCOUNT()

Database

Counts numeric cells in a column that match specified criteria

Syntax:

=DCOUNT(database, field, criteria)

Examples:

=DCOUNT(A1:D100, "Amount", F1:F2)

Count numeric Amount values matching criteria

Note: Use DCOUNTA to count non-empty cells (including text)

DMAX()

Database

Returns the maximum value in a column that matches specified criteria

Syntax:

=DMAX(database, field, criteria)

Examples:

=DMAX(A1:D100, "Revenue", F1:F2)

Max Revenue for matching rows

Note: Useful when you need a conditional MAX with multiple complex criteria

DMIN()

Database

Returns the minimum value in a column that matches specified criteria

Syntax:

=DMIN(database, field, criteria)

Examples:

=DMIN(A1:D100, "Cost", F1:F2)

Minimum Cost for matching rows

Note: Counterpart to DMAX

REGEXEXTRACT()

Text

Extracts text matching a regular expression

Syntax:

=REGEXEXTRACT(text, regular_expression)

Examples:

=REGEXEXTRACT(A1, "[0-9]+")

Extract first number from text

=REGEXEXTRACT(A1, "[A-Z]+")

Extract uppercase letters

=REGEXEXTRACT(A1, "\d{4}")

Extract 4-digit year

Note: Google Sheets exclusive; uses RE2 regex syntax

INDEX()

Lookup & Reference

Returns the value of a cell at a given row and column position in a range

Syntax:

=INDEX(reference, row, [column])

Examples:

=INDEX(A1:C10, 3, 2)

Value at row 3, column 2 of A1:C10

=INDEX(A:A, MATCH("Smith",B:B,0))

Paired with MATCH for flexible lookup

=INDEX(A1:A10, RANDBETWEEN(1, 10))

Random value from range

Note: Often paired with MATCH as a more flexible alternative to VLOOKUP

IFNA()

Logical

Returns a value only if the formula results in #N/A error

Syntax:

=IFNA(value, value_if_na)

Examples:

=IFNA(VLOOKUP(A1,B:C,2,0), "Not found")

Handle missing lookup

=IFNA(MATCH(A1,B:B,0), "No match")

Handle failed MATCH

Note: More precise than IFERROR — only handles #N/A, not all errors

COUNTIF()

Math & Statistical

Counts cells that meet a single criterion

Syntax:

=COUNTIF(range, criterion)

Examples:

=COUNTIF(A1:A10, ">100")

Count cells greater than 100

=COUNTIF(A1:A10, "Apple")

Count cells equal to 'Apple'

=COUNTIF(A1:A10, "<>"&"")

Count non-empty cells

Note: Use wildcards (* ?) for partial matching

AVERAGEIFS()

Math & Statistical

Calculates the average of cells that meet multiple criteria

Syntax:

=AVERAGEIFS(average_range, criteria_range1, criterion1, ...)

Examples:

=AVERAGEIFS(C1:C10, A1:A10, "A", B1:B10, ">50")

Average C where A='A' and B>50

=AVERAGEIFS(D:D, A:A, "North", B:B, 2024)

Average D for North region in 2024

Note: Returns #DIV/0! if no cells match the criteria

VLOOKUP()

Lookup & Reference

Searches a column and returns a value from the same row in another column

Syntax:

=VLOOKUP(search_key, range, index, [is_sorted])

Examples:

=VLOOKUP(A1, B:D, 2, FALSE)

Find A1 in column B, return column C

=VLOOKUP("Smith", A:C, 3, FALSE)

Look up 'Smith' in A, return column C

=IFERROR(VLOOKUP(A1,B:C,2,0),"Not found")

Safe VLOOKUP with fallback

Note: Use FALSE for exact match; searches first column only; prefer XLOOKUP when available

PMT()

Financial

Calculates the periodic payment for a loan or investment

Syntax:

=PMT(rate, number_of_periods, present_value, [future_value], [end_or_beginning])

Examples:

=PMT(0.05/12, 60, -10000)

Monthly payment on £10k loan, 5% annual, 5 years

=PMT(A1/12, B1*12, -C1)

Dynamic loan calculator

Note: Rate must match payment frequency; use monthly rate for monthly payments

Google Sheets Mastery Guide

Beginner

Start with calculations and basic data organisation

Intermediate

Master lookups and Google-specific power features

Advanced

QUERY language, regex, and live data imports

📊 What is Google Sheets?

Google Sheets is a free, cloud-based spreadsheet application from Google, available as part of Google Workspace. First launched in 2006, it has grown into a powerful alternative to Microsoft Excel — and in several areas surpasses it, with unique features like real-time collaboration, built-in QUERY language, live data imports, and formulas for translating text and fetching stock prices directly in a cell.

🚀 Core Capabilities

  • ✓ Real-time collaboration: Multiple users edit simultaneously
  • ✓ Cloud storage: Auto-saves to Google Drive
  • ✓ QUERY formula: SQL-like data analysis in a cell
  • ✓ Live data imports: IMPORTHTML, IMPORTXML, GOOGLEFINANCE
  • ✓ ARRAYFORMULA: Apply formulas to entire columns instantly
  • ✓ Apps Script: JavaScript-based automation and macros

💼 Google Sheets Exclusive Functions

  • ⭐ QUERY: SQL-like language for filtering and aggregating data
  • ⭐ ARRAYFORMULA: Array operations without Ctrl+Shift+Enter
  • ⭐ IMPORTRANGE: Pull data live from another spreadsheet
  • ⭐ GOOGLETRANSLATE: Translate text between 100+ languages
  • ⭐ GOOGLEFINANCE: Live stock prices and financial data
  • ⭐ SPARKLINE: Mini charts directly inside a cell

⚡ Google Sheets vs. Excel

Google Sheets Advantages

  • • Free to use with a Google account
  • • Real-time multi-user collaboration
  • • QUERY, ARRAYFORMULA, FILTER, UNIQUE built-in
  • • Live web data imports (IMPORTHTML, IMPORTXML)
  • • Version history with per-cell tracking
  • • Works in any browser, no install needed

Excel Advantages

  • • More powerful PivotTables and data model
  • • Better performance with very large datasets
  • • VBA macros for complex automation
  • • More chart types and formatting options
  • • Power Query for ETL operations
  • • Industry standard in finance and enterprise

Google Sheets Pro Tips

ARRAYFORMULA shortcut: Press Ctrl+Shift+Enter in a cell to automatically wrap your formula with ARRAYFORMULA — applies it to the entire column instantly.

QUERY SQL-like syntax: Use SELECT, WHERE, GROUP BY, ORDER BY, LIMIT just like SQL — e.g. QUERY(A:D, "SELECT A, SUM(D) GROUP BY A")

IMPORTRANGE cross-sheet tip: Paste the full Google Sheets URL as the first argument. You only need to grant access once — after that, data syncs live between spreadsheets automatically.

FILTER with OR logic: Use + between conditions (not OR()) — e.g. FILTER(A:B, (A:A="X")+(B:B="Y"))

Master Google Sheets formulas with practice. Bookmark this page for quick reference! ⭐ marks Google-exclusive functions.

📊 99 Total Formulas 🏷️ 10 Categories ⭐ Google-Exclusive Functions