Google Sheets Functions, Explained Properly

Searches the first row of a range and returns a value from the same column in a specified row. The horizontal sibling of VLOOKUP — rarely needed in modern sheets but still in use.

Returns the value of a cell at a specified row and column within a range. Paired with MATCH for the classic INDEX/MATCH lookup pattern that predates XLOOKUP and remains widely used.

Returns the position of a value in a one-dimensional range. Usually paired with INDEX to perform lookups, or used standalone to find where a value lives in a list.

Looks up a value in the first column of a range and returns a value from a column you specify in the same row. The original Google Sheets lookup function — still the most-used despite XLOOKUP arriving as a successor.

Modern replacement for VLOOKUP, HLOOKUP, and INDEX/MATCH. Searches a range or array for a match and returns the corresponding item from a second range or array. Handles "not found" inline, can search in any direction, and defaults to exact match.

Returns TRUE if ALL arguments are TRUE.

Returns one value when a condition is true and a different value when false. The foundation of conditional logic in spreadsheets and the most-used Google Sheets function after SUM.

Returns a fallback value when a formula returns an error, otherwise returns the formula's result. The defensive wrapper that turns ugly error codes into clean output.

Evaluates multiple conditions in order and returns the value associated with the first TRUE condition. The clean replacement for nested IFs when you have 3 or more cases.

Returns TRUE if any argument is TRUE.

Returns the value matching the first matching case. Lookup-style branching for equality matches.

Returns the absolute value of a number.

Returns the remainder of a division. Used for divisibility tests, cycles, and conditional formatting stripes.

Rounds a number to a specified number of decimal places using standard half-away-from-zero rounding. The everyday rounding function.

Rounds a number TOWARD zero to a specified number of decimal places.

Rounds a number AWAY from zero to a specified number of decimal places.

Adds up numbers in a range or list of values. The first function nearly every spreadsheet user learns and still the most-used function on the planet.

Adds the values in a range that meet a single condition. The conditional version of SUM, used for category totals, filtered sums, and rolling running balances.

Sums values that meet multiple conditions. The multi-criteria version of SUMIF, and the cleanest way to get filtered totals from a flat table.

Multiplies corresponding elements in arrays and returns the sum. Used for weighted averages and complex conditional sums.

Returns the arithmetic mean of numeric values in a range. Ignores text and blanks but treats booleans as 0/1 in some cases — usually what you want.

Returns the average of values in a range that meet a single condition. The conditional version of AVERAGE.

Returns the average of values that meet multiple conditions.

Counts the number of non-empty cells in a range — text, numbers, dates, even error values. Use this when you want to know "how many cells have something in them."

Counts the cells in a range that meet a single condition. The most-used way to answer "how many of X are there?" in a spreadsheet.

Counts cells that meet multiple conditions across multiple ranges. The multi-criteria version of COUNTIF, used for filtered counts and category breakdowns.

Returns the largest numeric value in a range or set of arguments. Used for peaks, ceilings, and bounding values.

Returns the smallest numeric value in a range or set of arguments. The mirror of MAX, commonly used to find lows or enforce ceilings on values.

Joins multiple text values into one string. The classic way to combine fields, but TEXTJOIN is usually a better choice for ranges. Still widely used for fixed-field combinations.

Returns position of one string in another, case-sensitively.

Returns the leftmost N characters of a string. The simplest text-extraction function — useful for prefixes, area codes, initials, and quick parsing.

Returns the number of characters in a string. Used for validation, truncation logic, and detecting unexpectedly long or short values.

Converts text to lowercase. Used for email normalization and case-insensitive dedup.

Extracts the first substring of text matching a regular expression. The Swiss army knife of text parsing in Google Sheets.

Returns TRUE if a string matches a regex pattern, FALSE otherwise. The boolean version of REGEXEXTRACT — useful in conditionals and FILTER.

Replaces text matching a regex pattern with a substitution. Regex version of SUBSTITUTE.

Returns the rightmost N characters of a string. The mirror of LEFT — used for file extensions, last names from a delimited string, suffixes, and quick parses.

Returns position of one string in another, case-insensitively. Supports * and ? wildcards.

Divides text around specified delimiters and spills the parts into separate cells. The inverse of TEXTJOIN — turns one string into many.

Replaces specified text with new text, optionally only the Nth occurrence. Find-and-replace without regex.

Concatenates a range of text values using a delimiter between each. The modern way to join strings — replaces clunky CONCATENATE chains for ranges.

Removes leading, trailing, and duplicate internal whitespace from a text value. The first cleanup function to reach for when imported data has hidden spaces breaking your lookups.

Converts text to uppercase.

Constructs a date value from year, month, and day numbers.

Calculates the difference between two dates in years, months, or days. The cleanest way to compute age, tenure, or any "how long between" metric.

Returns a date that is a specified number of months before or after a start date. The cleanest way to compute "next month", "3 months ago", or any month-shifted date.

Counts the number of business days between two dates, excluding weekends and optional holidays.

Returns the current date AND time. Updates on every recalculation.

Returns the current date with no time component. Updates automatically when the sheet recalculates. The simplest building block for date math and aging.

Returns a date N business days before or after a start date. Excludes weekends and optional holidays.

Applies a formula to an entire range at once, returning an array of results that 'spills' into the surrounding cells. Lets you write a single formula in one cell instead of dragging it down 10,000 rows.

Returns a filtered version of a source range that satisfies one or more conditions. The simplest way to extract matching rows without writing a QUERY string.

Runs a SQL-like query against a range. Google Sheets' most powerful function for filtering, sorting, aggregating, and reshaping data. The single most underused tool in the entire Sheets toolkit.

Returns a sorted copy of a range, sorted by one or more columns ascending or descending. Doesn't modify the source — produces a new array.

Renders a tiny inline chart inside a single cell. Bar charts, line charts, win/loss markers, and progress bars — all without leaving the cell. Perfect for dashboards and trend columns.

Flips an array's rows and columns. Useful for converting horizontal output to vertical (or vice versa), reorienting imports, and reshaping data for charts.

Returns the distinct rows from a range, removing duplicates. Used for deduplication, finding distinct categories, or building lookup tables from raw data.

Fetches current or historical securities data from Google Finance. Stock prices, currency rates, indices, and mutual fund data — built into Google Sheets, no API key needed.

Imports a CSV or TSV file from a URL. Simpler than IMPORTHTML for already-structured data.

Imports a table or list from a web page. The simplest way to scrape structured data from a public webpage without writing code.

Imports a range of cells from another Google Sheet. The bridge between sheets — used for cross-spreadsheet dashboards, shared reference data, and consolidating data from multiple files.

Imports data from any XML, HTML, or RSS/Atom URL using an XPath expression. More precise than IMPORTHTML when you need a specific element.

Defines a reusable function inline. Combined with MAP, REDUCE, BYROW, and BYCOL, this brings functional programming to Sheets. The most powerful modern addition to the formula language.

Assigns names to expressions and reuses them within a single formula. Makes complex formulas vastly more readable and faster — repeated subexpressions are computed only once.

Applies a LAMBDA to every element of one or more arrays and returns an array of results. The modern, explicit alternative to ARRAYFORMULA for per-element transforms.

Folds an array into a single value by applying a LAMBDA cumulatively. The functional-programming pattern for running totals, custom aggregates, and any "summarize this list" calculation.

Converts a text string into a cell reference. Lets you build dynamic references based on cell values — powerful for templated sheets but also a common cause of slowdowns when overused.

Returns a range that is N rows/columns from a starting cell, with optional dimensions. Used for dynamic ranges in charts.