Pick the task you're trying to do and get the exact formula — with a worked example, alternatives, and an AI generator for your own data.
Look up a value from another sheet
=VLOOKUP(A2, Sheet2!$A:$C, 3, FALSE)Look up a value with INDEX/MATCH
=INDEX(C:C, MATCH(A2, B:B, 0))Two-way lookup (row and column)
=INDEX(B2:E10, MATCH(G1, A2:A10, 0), MATCH(H1, B1:E1, 0))Look up a value with XLOOKUP
=XLOOKUP(A2, Products, Prices)Look up the last matching value
=XLOOKUP(A2, B:B, C:C, , 0, -1)Look up with two criteria
=XLOOKUP(A2&"|"&B2, Region&"|"&Month, Sales)Filter rows by criteria with a formula
=FILTER(A2:C100, B2:B100>100)Check if a value exists in a list
=IF(COUNTIF(B:B, A2)>0, "Yes", "No")Sum the same cell across sheets
=SUM(Jan:Dec!B2)Reference a sheet named in a cell
=INDIRECT("'"&A2&"'!B2")Horizontal lookup (HLOOKUP)
=HLOOKUP(A2, Table, 2, FALSE)Transpose rows to columns
=TRANSPOSE(A2:A10)Approximate match lookup (brackets)
=VLOOKUP(A2, Brackets, 2, TRUE)Find the first non-blank value
=XLOOKUP(TRUE, A2:A100<>"", A2:A100)Count rows or columns in a range
=ROWS(A2:A100)Build a dynamic range with OFFSET
=SUM(OFFSET(A1, 0, 0, COUNT(A:A), 1))Get a value by row and column number
=INDEX(Data, 3, 2)Look up with a partial match (wildcard)
=XLOOKUP("*"&A2&"*", Names, Ids, , 2)Pick from a list by number (CHOOSE)
=CHOOSE(A2, "Low", "Medium", "High")Look up and return an entire row
=XLOOKUP(A2, Ids, DataRange)Sum with multiple conditions
=SUMIFS(C:C, A:A, "East", B:B, ">100")Count cells that meet a condition
=COUNTIF(A:A, "Done")Count unique values
=COUNTA(UNIQUE(A2:A100))Average with a condition
=AVERAGEIF(A:A, "East", C:C)Running total (cumulative sum)
=SUM($B$2:B2)Percentage of total
=B2/SUM($B$2:$B$100)Percentage change between two values
=(B2-A2)/A2Round a number to set decimals
=ROUND(A2, 2)Rank a list of values
=RANK.EQ(A2, $A$2:$A$100)Max or min with a condition
=MAXIFS(C:C, A:A, "East")Weighted average
=SUMPRODUCT(B2:B10, C2:C10)/SUM(C2:C10)Sum the top N values
=SUM(LARGE(B2:B100, {1,2,3}))Count items in a date range
=COUNTIFS(D:D, ">="&E1, D:D, "<="&E2)Multiply two columns and sum
=SUMPRODUCT(A2:A100, B2:B100)Count blank or non-blank cells
=COUNTBLANK(A2:A100)Round to the nearest multiple
=MROUND(A2, 5)Get the remainder or whole number
=MOD(A2, B2)Get the absolute value
=ABS(A2)Subtotal that ignores filtered rows
=SUBTOTAL(109, B2:B100)Convert text to a number
=VALUE(A2)Cap a value between a min and max
=MIN(MAX(A2, 0), 100)Generate a sequence of numbers
=SEQUENCE(10)Sum or count matching any of several values
=SUM(COUNTIF(A:A, {"Open","Pending","Review"}))Convert units of measure
=CONVERT(A2, "mi", "km")Multiply all numbers in a range
=PRODUCT(A2:A10)Square root or nth root
=SQRT(A2)Combine text from multiple cells
=TEXTJOIN(" ", TRUE, A2, B2)Split text into separate columns
=TEXTSPLIT(A2, " ")Remove extra spaces from text
=TRIM(A2)Change text to proper/upper/lower case
=PROPER(A2)Extract the domain from an email address
=MID(A2, FIND("@", A2)+1, LEN(A2))Count characters in a cell
=LEN(A2)Count words in a cell
=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2, " ", ""))+1Find the position of a character
=FIND("-", A2)Replace part of a text string
=SUBSTITUTE(A2, "old", "new")Add leading zeros to numbers
=TEXT(A2, "00000")Extract the last word from text
=TRIM(RIGHT(SUBSTITUTE(A2, " ", REPT(" ", 100)), 100))Extract text between two characters
=TEXTAFTER(TEXTBEFORE(A2, ")"), "(")Extract the first N characters
=LEFT(A2, 5)Capitalize only the first letter
=UPPER(LEFT(A2,1))&MID(A2,2,LEN(A2))Check if text contains any word from a list
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(Words, A2)))>0, "Yes", "No")Count occurrences of a character
=LEN(A2)-LEN(SUBSTITUTE(A2, ",", ""))Compare two cells for a match
=EXACT(A2, B2)Repeat text or make an in-cell bar
=REPT("★", A2)Remove non-printable characters
=CLEAN(TRIM(A2))Extract text before a delimiter
=TEXTBEFORE(A2, "-")Extract a file extension
=TEXTAFTER(A2, ".", -1)Replace the nth occurrence of text
=SUBSTITUTE(A2, " ", "-", 2)Days between two dates
=B2-A2Calculate age from a birth date
=DATEDIF(A2, TODAY(), "Y")Add or subtract months from a date
=EDATE(A2, 3)Get the month name from a date
=TEXT(A2, "mmmm")Count working days between dates
=NETWORKDAYS(A2, B2)Insert today's date or current time
=TODAY()Get the quarter from a date
=ROUNDUP(MONTH(A2)/3, 0)First or last day of the month
=EOMONTH(A2, 0)Get the week number from a date
=ISOWEEKNUM(A2)Convert text to a real date
=DATEVALUE(A2)Get the day of the week
=TEXT(A2, "dddd")Time difference in hours
=(B2-A2)*24Days until a future date
=A2-TODAY()Add business days to a date
=WORKDAY(A2, 10)Format a date as text
=TEXT(A2, "yyyy-mm-dd")Number of days in a month
=DAY(EOMONTH(A2, 0))First day of the week for a date
=A2-WEEKDAY(A2, 2)+1Years of service (tenure)
=DATEDIF(A2, TODAY(), "y") & "y " & DATEDIF(A2, TODAY(), "ym") & "m"Show elapsed time over 24 hours
=TEXT(B2-A2, "[h]:mm")Duration in months between dates
=DATEDIF(A2, B2, "m")Count a specific weekday in a date range
=NETWORKDAYS.INTL(A2, B2, "0111111")Convert a number of hours to a time
=A2/24Return a value if a cell contains text
=IF(ISNUMBER(SEARCH("urgent", A2)), "Yes", "No")Assign grades or tiers with nested logic
=IFS(A2>=90, "A", A2>=80, "B", A2>=70, "C", TRUE, "F")Flag or handle blank cells
=IF(A2="", "Missing", A2)Get a list of values without duplicates
=UNIQUE(A2:A100)IF with AND / OR conditions
=IF(AND(A2>0, B2="Yes"), "OK", "No")Hide errors with IFERROR
=IFERROR(A2/B2, 0)VLOOKUP that returns blank, not #N/A
=IFERROR(VLOOKUP(A2, Table, 2, FALSE), "")Map values with SWITCH
=SWITCH(A2, "N", "North", "S", "South", "Other")Flag duplicate values
=IF(COUNTIF($A$2:A2, A2)>1, "Duplicate", "")Count how many times a value repeats
=COUNTIF(A:A, A2)Show a blank instead of zero
=IF(A2=0, "", A2)Try multiple lookups in order
=IFERROR(VLOOKUP(A2, T1, 2, 0), IFERROR(VLOOKUP(A2, T2, 2, 0), "Not found"))Check if all cells in a range match
=IF(COUNTIF(B2:B10, "Yes")=COUNTA(B2:B10), "All", "Not all")Check if a number is even or odd
=IF(ISEVEN(A2), "Even", "Odd")Return the column header of the max value
=INDEX($B$1:$E$1, MATCH(MAX(B2:E2), B2:E2, 0))Number each occurrence of a value
=COUNTIF($A$2:A2, A2)Standard deviation
=STDEV.S(A2:A100)Median value
=MEDIAN(A2:A100)Percentile of a dataset
=PERCENTILE.INC(A2:A100, 0.9)Correlation between two columns
=CORREL(A2:A100, B2:B100)Forecast a future value
=FORECAST.LINEAR(x, known_ys, known_xs)Count values above the average
=COUNTIF(A2:A100, ">"&AVERAGE(A2:A100))Calculate a z-score
=(A2-AVERAGE($A$2:$A$100))/STDEV.S($A$2:$A$100)Moving average
=AVERAGE(B2:B4)Most frequent value (mode)
=MODE.SNGL(A2:A100)Average ignoring outliers (trimmed mean)
=TRIMMEAN(A2:A100, 0.1)Rank a value as a percentile
=PERCENTRANK.INC(A2:A100, B2)Monthly loan payment
=PMT(rate/12, nper, -principal)Compound interest
=A2*(1+B2)^C2Future value of regular savings
=FV(rate/12, years*12, -payment, -initial)Profit margin percentage
=(B2-A2)/B2Compound annual growth rate (CAGR)
=(B2/A2)^(1/C2)-1Apply a percentage discount
=A2*(1-B2)Simple interest
=A2*B2*C2Straight-line depreciation
=SLN(cost, salvage, life)Net present value (NPV)
=NPV(B1, B3:B12)+B2Browse the tasks below by category, or describe what you want in plain language to ExcelGPT and it will write the formula, explain how it works, and verify it against your data.
Most do — functions like SUMIFS, VLOOKUP, INDEX/MATCH, and TEXT behave the same. A few newer ones (UNIQUE, TEXTSPLIT, IFS) exist in both, and we note version differences on each page.