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.
IF()
LogicalReturns one value if a condition is true, another if false
Syntax:
=IF(logical_expression, value_if_true, value_if_false)Examples:
=IF(A1>100, "High", "Low") Label values above 100 as High
=IF(B1="", "Empty", B1) Show 'Empty' for blank cells
=IF(A1>90, "A", IF(A1>80, "B", "C")) Nested IF for grades
Note: Use IFS() for multiple conditions to avoid deep nesting
IFERROR()
LogicalReturns a specified value if a formula results in an error
Syntax:
=IFERROR(value, value_if_error)Examples:
=IFERROR(A1/B1, 0) Return 0 instead of #DIV/0!
=IFERROR(VLOOKUP(A1,B:C,2,0), "Not found") Handle missing VLOOKUP result
=IFERROR(VALUE(A1), 0) Default to 0 if A1 isn't a number
Note: Catches all error types including #N/A, #VALUE!, #REF!, #DIV/0!
DATEDIF()
Date & TimeCalculates the difference between two dates in specified units
Syntax:
=DATEDIF(start_date, end_date, unit)Examples:
=DATEDIF(A1, TODAY(), "Y") Age in complete years
=DATEDIF(A1, B1, "M") Months between two dates
=DATEDIF(A1, B1, "D") Days between two dates
Note: Units: Y (years), M (months), D (days), YM, YD, MD for partial periods
IFNA()
LogicalReturns 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
SUMIF()
Math & StatisticalSums cells that meet a single criterion
Syntax:
=SUMIF(range, criterion, [sum_range])Examples:
=SUMIF(A1:A10, ">100") Sum values greater than 100
=SUMIF(A1:A10, "Apple", B1:B10) Sum B values where A contains 'Apple'
=SUMIF(A1:A10, A12, B1:B10) Sum B where A equals A12 value
Note: If sum_range is omitted, the range itself is summed
COUNTIF()
Math & StatisticalCounts 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
SUMIFS()
Math & StatisticalSums cells that meet multiple criteria
Syntax:
=SUMIFS(sum_range, criteria_range1, criterion1, ...)Examples:
=SUMIFS(C1:C10, A1:A10, "Apple", B1:B10, ">100") Sum C where A='Apple' AND B>100
=SUMIFS(D1:D10, B1:B10, "North", C1:C10, "Q1") Sum D for North region in Q1
=SUMIFS(C:C, A:A, "*Pro*", B:B, ">0") Sum C where A contains 'Pro' and B>0
Note: All criteria must be met simultaneously for a cell to be included
AVERAGEIFS()
Math & StatisticalCalculates 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
IFS()
LogicalTests multiple conditions and returns a value for the first true one
Syntax:
=IFS(condition1, value1, [condition2, value2], ...)Examples:
=IFS(A1>90,"A",A1>80,"B",A1>70,"C",TRUE,"F") Grade A/B/C/F from score
=IFS(B1="AM","Morning",B1="PM","Afternoon",TRUE,"Unknown") Map AM/PM to labels
Note: Add TRUE as final condition as a catch-all default
ISERROR()
InformationReturns TRUE if the value is any error
Syntax:
=ISERROR(value)Examples:
=ISERROR(A1/B1) TRUE if division causes an error
=IF(ISERROR(A1), 0, A1) Replace errors with 0
=ISERROR(VLOOKUP(A1, B:C, 2, 0)) Check if lookup fails
Note: Detects all error types; prefer IFERROR for cleaner error handling
ISBLANK()
InformationReturns TRUE if the cell is empty
Syntax:
=ISBLANK(value)Examples:
=ISBLANK(A1) TRUE if A1 is empty
=IF(ISBLANK(A1), "No data", A1) Show 'No data' for empty cells
=COUNTIF(A1:A10, "") Count blanks (alternative approach)
Note: A cell with a formula returning empty string is not considered blank
AND()
LogicalReturns TRUE if all conditions are true
Syntax:
=AND(logical1, [logical2], ...)Examples:
=AND(A1>0, A1<100) Check if A1 is between 0 and 100
=IF(AND(A1>50, B1="Yes"), "Pass", "Fail") Multi-condition IF
=AND(ISNUMBER(A1), A1>=0) Check positive number
Note: All conditions must be TRUE for AND to return TRUE
ISNUMBER()
InformationReturns TRUE if the value is a number
Syntax:
=ISNUMBER(value)Examples:
=ISNUMBER(A1) TRUE if A1 contains a number
=IF(ISNUMBER(A1), A1*2, "Not a number") Conditional calculation
=ISNUMBER(SEARCH("error", A1)) Check if 'error' appears in A1
Note: Dates and times are stored as numbers and return TRUE
OR()
LogicalReturns TRUE if any condition is true
Syntax:
=OR(logical1, [logical2], ...)Examples:
=OR(A1="Yes", A1="Y") Check for multiple valid inputs
=IF(OR(A1="", A1=0), "Empty", A1) Handle blank or zero
=OR(A1>100, B1>100) Either A1 or B1 exceeds 100
Note: Returns TRUE if at least one condition is TRUE
ISTEXT()
InformationReturns TRUE if the value is text
Syntax:
=ISTEXT(value)Examples:
=ISTEXT(A1) TRUE if A1 is text
=IF(ISTEXT(A1), UPPER(A1), A1) Uppercase only text cells
Note: Numbers stored as text return TRUE
ARRAYFORMULA()
Google-Specific ⭐Enables a formula to return multiple results across a range
Syntax:
=ARRAYFORMULA(array_formula)Examples:
=ARRAYFORMULA(A1:A10*B1:B10) Multiply columns element-wise
=ARRAYFORMULA(IF(A2:A100="","",A2:A100*1.2)) Apply formula to entire column
=ARRAYFORMULA(SUM(IF(A1:A10>5,A1:A10,0))) Conditional sum with array logic
Note: ⭐ Google Sheets exclusive; shortcut Ctrl+Shift+Enter auto-wraps with ARRAYFORMULA
FILTER()
Google-Specific ⭐Returns only the rows in a range that meet specified conditions
Syntax:
=FILTER(range, condition1, [condition2], ...)Examples:
=FILTER(A1:C10, B1:B10>50) Rows where column B > 50
=FILTER(A:C, A:A="North", C:C>100) Multiple conditions (AND)
=FILTER(A:A, (B:B="Y")+(C:C="Y")) OR condition using addition
Note: ⭐ Google Sheets exclusive; results spill automatically into adjacent cells
DGET()
DatabaseExtracts 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
XOR()
LogicalReturns TRUE if an odd number of conditions are true
Syntax:
=XOR(logical1, [logical2], ...)Examples:
=XOR(A1>0, B1>0) TRUE if exactly one of A1,B1 is positive
=XOR(TRUE, FALSE) Returns TRUE
=XOR(TRUE, TRUE) Returns FALSE (both true = even count)
Note: Useful for exclusive-or logic where exactly one condition should be true
DEC2BIN()
EngineeringConverts a decimal number to binary
Syntax:
=DEC2BIN(decimal_number, [significant_digits])Examples:
=DEC2BIN(10) Returns '1010'
=DEC2BIN(255, 8) Returns '11111111' (8 digits)
Note: significant_digits pads with leading zeros
ROUND()
Math & StatisticalRounds a number to a specified number of digits
Syntax:
=ROUND(value, places)Examples:
=ROUND(3.14159, 2) Returns 3.14
=ROUND(A1, 0) Round to nearest integer
=ROUND(1234, -2) Round to nearest 100 → 1200
Note: Negative places rounds to the left of the decimal point
FLOOR()
Math & StatisticalRounds a number down to the nearest multiple of significance
Syntax:
=FLOOR(value, factor)Examples:
=FLOOR(3.7, 1) Returns 3
=FLOOR(7.5, 0.5) Returns 7.5
=FLOOR(24, 5) Returns 20
Note: Always rounds toward zero
CEILING()
Math & StatisticalRounds a number up to the nearest multiple of significance
Syntax:
=CEILING(value, factor)Examples:
=CEILING(3.2, 1) Returns 4
=CEILING(6.1, 0.5) Returns 6.5
=CEILING(23, 5) Returns 25
Note: Always rounds away from zero
LEFT()
TextReturns a specified number of characters from the start of a string
Syntax:
=LEFT(string, [number_of_characters])Examples:
=LEFT(A1, 3) First 3 characters of A1
=LEFT(A1, FIND(" ", A1)-1) Extract first word
=LEFT("Hello", 4) Returns 'Hell'
Note: Default number_of_characters is 1
RIGHT()
TextReturns a specified number of characters from the end of a string
Syntax:
=RIGHT(string, [number_of_characters])Examples:
=RIGHT(A1, 4) Last 4 characters of A1
=RIGHT(A1, LEN(A1)-FIND(" ",A1)) Extract last word
=RIGHT("Invoice-2024", 4) Returns '2024'
Note: Default number_of_characters is 1
EDATE()
Date & TimeReturns a date a specified number of months before/after a start date
Syntax:
=EDATE(start_date, months)Examples:
=EDATE(A1, 3) 3 months after date in A1
=EDATE(TODAY(), -1) Same day last month
=EDATE(A1, 12) 1 year after A1
Note: Negative months go backward in time
EOMONTH()
Date & TimeReturns the last day of the month a specified number of months away
Syntax:
=EOMONTH(start_date, months)Examples:
=EOMONTH(TODAY(), 0) Last day of current month
=EOMONTH(A1, 1) Last day of next month from A1
=EOMONTH(A1, -1)+1 First day of current month
Note: months=0 returns end of current month
WORKDAY()
Date & TimeReturns a date a specified number of working days from a start date
Syntax:
=WORKDAY(start_date, num_days, [holidays])Examples:
=WORKDAY(TODAY(), 5) 5 business days from today
=WORKDAY(A1, -3) 3 business days before A1
=WORKDAY(A1, 10, D1:D5) 10 business days, excluding holidays
Note: Skips weekends automatically; provide holidays list to also skip those
INDIRECT()
Lookup & ReferenceReturns a reference specified by a text string
Syntax:
=INDIRECT(cell_reference_as_text)Examples:
=INDIRECT("A"&B1) Reference cell A[value in B1]
=SUM(INDIRECT(A1&":"&B1)) Sum dynamic range from text
=INDIRECT("Sheet2!A1") Reference another sheet dynamically
Note: Volatile function — use sparingly; useful for dynamic range references
ISLOGICAL()
InformationReturns TRUE if the value is a logical (TRUE/FALSE) value
Syntax:
=ISLOGICAL(value)Examples:
=ISLOGICAL(TRUE) Returns TRUE
=ISLOGICAL(A1>5) Returns TRUE (expression is logical)
Note: Only returns TRUE for TRUE or FALSE values, not 1 or 0
DSUM()
DatabaseSums 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
DAVERAGE()
DatabaseAverages 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()
DatabaseCounts 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()
DatabaseReturns 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()
DatabaseReturns 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
NOT()
LogicalReverses the logical value of its argument
Syntax:
=NOT(logical_expression)Examples:
=NOT(ISBLANK(A1)) Check if A1 is not blank
=NOT(A1=B1) Equivalent to A1<>B1
=IF(NOT(A1>100), "OK", "Too high") Invert condition in IF
Note: Converts TRUE to FALSE and FALSE to TRUE
WEEKDAY()
Date & TimeReturns the day of the week as a number
Syntax:
=WEEKDAY(date, [type])Examples:
=WEEKDAY(TODAY()) Day of week (1=Sun to 7=Sat by default)
=WEEKDAY(A1, 2) Type 2: 1=Mon to 7=Sun
=IF(WEEKDAY(A1,2)>5, "Weekend", "Weekday") Classify as weekday or weekend
Note: Type 1 (default): 1=Sunday. Type 2: 1=Monday. Type 3: 0=Monday
QUERY()
Google-Specific ⭐Runs a SQL-like query on your data using Google Visualization API query language
Syntax:
=QUERY(data, query, [headers])Examples:
=QUERY(A1:D100, "SELECT A, B WHERE C > 100") Select columns where C>100
=QUERY(A:D, "SELECT A, SUM(D) GROUP BY A ORDER BY SUM(D) DESC") Sum by group, sorted
=QUERY(A:C, "SELECT * WHERE B = '" & E1 & "'") Dynamic filter using cell value
Note: ⭐ Google Sheets exclusive; one of the most powerful functions for data analysis
UNIQUE()
Google-Specific ⭐Returns unique rows or values from a range, removing duplicates
Syntax:
=UNIQUE(range, [by_column], [exactly_once])Examples:
=UNIQUE(A1:A100) Unique values from column A
=UNIQUE(A1:C100) Unique rows across three columns
=SORT(UNIQUE(A1:A100)) Sorted unique values
Note: ⭐ Google Sheets exclusive; results spill automatically
SORT()
Google-Specific ⭐Sorts the rows of a range by one or more columns
Syntax:
=SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2], ...)Examples:
=SORT(A1:C10, 1, TRUE) Sort by first column ascending
=SORT(A1:C10, 2, FALSE) Sort by second column descending
=SORT(A1:C10, 1, TRUE, 2, FALSE) Sort by column 1 then 2
Note: ⭐ Google Sheets exclusive; spills automatically; pair with FILTER for dynamic tables
GOOGLEFINANCE()
Google-Specific ⭐Fetches live or historical stock/finance data from Google Finance
Syntax:
=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])Examples:
=GOOGLEFINANCE("GOOGL", "price") Current Google stock price
=GOOGLEFINANCE("AAPL", "volume") Apple trading volume
=GOOGLEFINANCE("GBPUSD", "price") GBP to USD exchange rate
Note: ⭐ Google Sheets exclusive; attributes: price, volume, high, low, open, close, pe, eps, marketcap
GOOGLETRANSLATE()
Google-Specific ⭐Translates text from one language to another using Google Translate
Syntax:
=GOOGLETRANSLATE(text, [source_language], [target_language])Examples:
=GOOGLETRANSLATE(A1, "en", "fr") Translate A1 to French
=GOOGLETRANSLATE(A1, "auto", "es") Auto-detect source, translate to Spanish
=GOOGLETRANSLATE("Hello", "en", "ja") Translate 'Hello' to Japanese
Note: ⭐ Google Sheets exclusive; use 'auto' for automatic source language detection
DETECTLANGUAGE()
Google-Specific ⭐Detects the language of text in a cell
Syntax:
=DETECTLANGUAGE(text_or_range)Examples:
=DETECTLANGUAGE(A1) Detect language of A1 (returns 'en', 'fr', etc.)
=GOOGLETRANSLATE(A1, DETECTLANGUAGE(A1), "en") Auto-translate to English
Note: ⭐ Google Sheets exclusive; returns ISO 639-1 language codes
IMPORTDATA()
Google-Specific ⭐Imports data from a CSV or TSV file at a given URL
Syntax:
=IMPORTDATA(url)Examples:
=IMPORTDATA("https://example.com/data.csv") Import CSV from web URL
=IMPORTDATA(A1) Import CSV from URL stored in A1
Note: ⭐ Google Sheets exclusive; refreshes automatically; supports CSV and TSV formats
IMPORTHTML()
Google-Specific ⭐Imports data from an HTML table or list on a web page
Syntax:
=IMPORTHTML(url, query, index)Examples:
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of...", "table", 1) Import first table from Wikipedia
=IMPORTHTML(A1, "list", 2) Import second list from URL in A1
Note: ⭐ Google Sheets exclusive; query can be 'table' or 'list'
IMPORTXML()
Google-Specific ⭐Imports data from any XML, HTML, CSV, TSV, or RSS feed
Syntax:
=IMPORTXML(url, xpath_query)Examples:
=IMPORTXML("https://feeds.bbci.co.uk/news/rss.xml", "//item/title") Import BBC news headlines
=IMPORTXML(A1, "//h1") Extract all H1 headings from a page
Note: ⭐ Google Sheets exclusive; uses XPath syntax for querying
IMAGE()
Google-Specific ⭐Inserts an image into a cell from a URL
Syntax:
=IMAGE(url, [mode], [height], [width])Examples:
=IMAGE("https://example.com/logo.png") Insert image, fit to cell
=IMAGE(A1, 2) Insert image, stretch to cell size
=IMAGE(A1, 4, 50, 80) Custom height 50, width 80 pixels
Note: ⭐ Google Sheets exclusive; mode: 1=fit, 2=stretch, 3=original size, 4=custom size
SPARKLINE()
Google-Specific ⭐Creates a miniature chart inside a single cell
Syntax:
=SPARKLINE(data, [options])Examples:
=SPARKLINE(A1:A10) Line sparkline for the range
=SPARKLINE(A1:A10, {"charttype","bar"}) Bar chart sparkline
=SPARKLINE(A1:A10, {"charttype","column";"color","blue"}) Blue column sparkline
Note: ⭐ Google Sheets exclusive; chart types: line, bar, column, winloss
VLOOKUP()
Lookup & ReferenceSearches 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
MEDIAN()
Math & StatisticalReturns the median (middle) value of a dataset
Syntax:
=MEDIAN(value1, [value2], ...)Examples:
=MEDIAN(A1:A10) Middle value in a sorted dataset
=MEDIAN(1,2,3,4,5) Returns 3
Note: If even number of values, averages the two middle values
FIND()
TextReturns the position of a string within another string (case-sensitive)
Syntax:
=FIND(search_for, text_to_search, [starting_at])Examples:
=FIND("@", A1) Find position of @ in email
=FIND(" ", A1) Find position of first space
=FIND(".", A1, FIND(".",A1)+1) Find second period
Note: Returns #VALUE! if not found; SEARCH() is the case-insensitive version
SWITCH()
LogicalEvaluates 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
NETWORKDAYS()
Date & TimeReturns the number of working days between two dates
Syntax:
=NETWORKDAYS(start_date, end_date, [holidays])Examples:
=NETWORKDAYS(A1, B1) Business days between A1 and B1
=NETWORKDAYS(TODAY(), A1, D1:D10) Working days to deadline, excluding holidays
Note: Excludes weekends (Sat/Sun) and optionally specified holidays
FV()
FinancialCalculates the future value of an investment
Syntax:
=FV(rate, number_of_periods, payment, [present_value], [end_or_beginning])Examples:
=FV(0.06/12, 120, -200) FV of £200/month for 10 years at 6%
=FV(0.05, 5, 0, -1000) FV of £1000 in 5 years at 5% annual
Note: Payments are negative if cash outflows; result is positive
BITXOR()
EngineeringPerforms a bitwise XOR on two numbers
Syntax:
=BITXOR(value1, value2)Examples:
=BITXOR(12, 10) Returns 6 (1100 XOR 1010 = 0110)
=BITXOR(255, 15) Returns 240
Note: Bits that differ result in 1; identical bits result in 0
SUM()
Math & StatisticalAdds all numbers in a range
Syntax:
=SUM(value1, [value2], ...)Examples:
=SUM(A1:A10) Sum all values in range A1 to A10
=SUM(A1, B1, C1) Sum specific cells
=SUM(A1:A5, C1:C5) Sum multiple ranges
Note: Ignores text values and empty cells
AVERAGE()
Math & StatisticalCalculates the arithmetic mean of numbers
Syntax:
=AVERAGE(value1, [value2], ...)Examples:
=AVERAGE(A1:A10) Average of range A1 to A10
=AVERAGE(A1, B1, C1) Average of specific cells
=AVERAGE(A:A) Average of entire column A
Note: Excludes empty cells and text from calculation
COUNT()
Math & StatisticalCounts the number of numeric values in a range
Syntax:
=COUNT(value1, [value2], ...)Examples:
=COUNT(A1:A10) Count numeric cells in range
=COUNT(A:A) Count numeric cells in entire column
=COUNT(A1, B1, C1) Count specific numeric cells
Note: Only counts cells with numeric values; use COUNTA for non-empty cells
MAX()
Math & StatisticalReturns the maximum value in a range
Syntax:
=MAX(value1, [value2], ...)Examples:
=MAX(A1:A10) Largest value in range
=MAX(A1:A10, B1:B10) Largest value across multiple ranges
=MAX(0, A1) Return A1 if positive, otherwise 0
Note: Ignores text and logical values
MOD()
Math & StatisticalReturns the remainder after dividing a number
Syntax:
=MOD(dividend, divisor)Examples:
=MOD(10, 3) Returns 1 (remainder of 10÷3)
=MOD(A1, 2) Check if A1 is odd (returns 1) or even (0)
=MOD(ROW(), 2) Alternate 1 and 0 for striped row logic
Note: Result has the same sign as the divisor
REGEXMATCH()
TextTests 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
XLOOKUP()
Lookup & ReferenceSearches 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
Google Sheets Mastery Guide
Beginner
Start with calculations and basic data organisation
Intermediate
Master lookups and Google-specific power features
- • VLOOKUP, INDEX-MATCH
- • FILTER, UNIQUE, SORT
- • ARRAYFORMULA basics
- • IMPORTRANGE cross-sheet data
Advanced
QUERY language, regex, and live data imports
- • QUERY with SQL-like syntax
- • REGEXEXTRACT, REGEXREPLACE
- • IMPORTHTML, IMPORTXML
- • GOOGLEFINANCE live data
📊 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.