Formula Library
38 of the most commonly used Excel & Google Sheets formulas — each with full syntax, a real example, and plain-English explanation.
VLOOKUPLookupSearches for a value in the first column of a range and returns a value in the same row from another column.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])Example
=VLOOKUP(A2, D:F, 2, FALSE)Looks up the value in A2 within column D, returns the corresponding value from the 2nd column (E) of the range D:F.
INDEX/MATCHLookupA powerful combination that returns a value from a table by matching both row and column. More flexible than VLOOKUP.
Syntax
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))Example
=INDEX(B:B, MATCH(E2, A:A, 0))Finds the row where E2 matches in column A, then returns the value from that same row in column B.
XLOOKUPLookupModern replacement for VLOOKUP/HLOOKUP. Searches a range and returns a match from another range.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Example
=XLOOKUP(A2, D:D, E:E, "Not found")Looks up A2 in column D and returns the matching value from column E, showing "Not found" if no match.
HLOOKUPLookupLike VLOOKUP but searches horizontally across rows instead of vertically down columns.
Syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])Example
=HLOOKUP("Q1", A1:D3, 2, FALSE)Finds "Q1" in row 1 of A1:D3 and returns the value from row 2 in the same column.
MATCHLookupReturns the relative position of a value within an array or range.
Syntax
=MATCH(lookup_value, lookup_array, [match_type])Example
=MATCH("Apple", A:A, 0)Returns the row number (position) where "Apple" is found in column A.
INDEXLookupReturns the value of a cell in a range, given a row and column number.
Syntax
=INDEX(array, row_num, [col_num])Example
=INDEX(A1:C10, 3, 2)Returns the value in row 3, column 2 of the range A1:C10.
SUMIFMath & StatisticsSums values in a range that meet a single condition.
Syntax
=SUMIF(range, criteria, [sum_range])Example
=SUMIF(A:A, "Approved", B:B)Sums all values in column B where the corresponding cell in column A equals "Approved".
SUMIFSMath & StatisticsSums values that meet multiple conditions across multiple ranges.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])Example
=SUMIFS(C:C, A:A, "Approved", B:B, "Q1")Sums values in column C where column A is "Approved" AND column B is "Q1".
COUNTIFMath & StatisticsCounts the number of cells that meet a single condition.
Syntax
=COUNTIF(range, criteria)Example
=COUNTIF(A:A, "Yes")Counts how many cells in column A contain the word "Yes".
COUNTIFSMath & StatisticsCounts cells that meet multiple conditions across multiple ranges.
Syntax
=COUNTIFS(range1, criteria1, [range2, criteria2, ...])Example
=COUNTIFS(A:A, "Open", B:B, ">100")Counts rows where column A is "Open" and column B is greater than 100.
AVERAGEMath & StatisticsCalculates the arithmetic mean of a range of numbers.
Syntax
=AVERAGE(number1, [number2, ...])Example
=AVERAGE(B2:B100)Calculates the average of all numeric values in cells B2 through B100.
AVERAGEIFMath & StatisticsAverages values in a range that meet a single condition.
Syntax
=AVERAGEIF(range, criteria, [average_range])Example
=AVERAGEIF(A:A, "North", B:B)Averages values in column B where the corresponding cell in column A equals "North".
ROUNDMath & StatisticsRounds a number to a specified number of digits.
Syntax
=ROUND(number, num_digits)Example
=ROUND(3.14159, 2)Rounds 3.14159 to 2 decimal places, returning 3.14.
IFLogicalReturns one value if a condition is true, and another value if it's false.
Syntax
=IF(logical_test, value_if_true, value_if_false)Example
=IF(A2>100, "High", "Low")Returns "High" if A2 is greater than 100, otherwise returns "Low".
IFSLogicalChecks multiple conditions and returns the value corresponding to the first true condition.
Syntax
=IFS(condition1, value1, condition2, value2, ...)Example
=IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",TRUE,"F")Returns a grade letter based on the score in A2, with TRUE as a catch-all default.
IFERRORLogicalReturns a custom value if a formula results in an error, otherwise returns the formula's result.
Syntax
=IFERROR(value, value_if_error)Example
=IFERROR(VLOOKUP(A2,D:E,2,FALSE), "Not found")Runs the VLOOKUP, but if it returns any error, shows "Not found" instead.
ANDLogicalReturns TRUE if all conditions are true, FALSE if any condition is false.
Syntax
=AND(logical1, [logical2, ...])Example
=AND(A2>0, B2="Active", C2<100)Returns TRUE only if A2 is positive, B2 is "Active", AND C2 is less than 100.
ORLogicalReturns TRUE if any condition is true, FALSE only if all conditions are false.
Syntax
=OR(logical1, [logical2, ...])Example
=OR(A2="Yes", A2="Maybe")Returns TRUE if A2 is either "Yes" or "Maybe".
CONCATENATE / CONCATTextJoins multiple text strings or cell values into a single string.
Syntax
=CONCAT(text1, [text2, ...]) or =A1&" "&B1Example
=CONCAT(A2, " ", B2)Joins the values of A2 and B2 with a space in between.
TEXTJOINTextJoins text from multiple cells with a specified delimiter, optionally ignoring empty cells.
Syntax
=TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...])Example
=TEXTJOIN(", ", TRUE, A2:A10)Joins all non-empty values in A2:A10 with a comma and space separator.
LEFTTextExtracts a specified number of characters from the left (beginning) of a text string.
Syntax
=LEFT(text, [num_chars])Example
=LEFT(A2, 3)Returns the first 3 characters from the text in A2.
RIGHTTextExtracts a specified number of characters from the right (end) of a text string.
Syntax
=RIGHT(text, [num_chars])Example
=RIGHT(A2, 4)Returns the last 4 characters from the text in A2.
MIDTextExtracts a specific number of characters from a text string, starting at a given position.
Syntax
=MID(text, start_num, num_chars)Example
=MID(A2, 4, 5)Returns 5 characters from A2, starting at the 4th character.
LENTextReturns the number of characters in a text string.
Syntax
=LEN(text)Example
=LEN(A2)Returns the total number of characters (including spaces) in the text in A2.
TRIMTextRemoves extra spaces from text, leaving only single spaces between words.
Syntax
=TRIM(text)Example
=TRIM(A2)Removes all leading, trailing, and extra internal spaces from the text in A2.
SUBSTITUTETextReplaces occurrences of a specific text string within another text string.
Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])Example
=SUBSTITUTE(A2, "Old", "New")Replaces all occurrences of "Old" with "New" in the text in A2.
FINDTextReturns the position (character number) of a text string within another text string. Case-sensitive.
Syntax
=FIND(find_text, within_text, [start_num])Example
=FIND("@", A2)Returns the position of the "@" character in A2 (useful for parsing email addresses).
TODAYDate & TimeReturns the current date, updated automatically each time the spreadsheet recalculates.
Syntax
=TODAY()Example
=TODAY()-A2Calculates the number of days between today and the date in A2.
DATEDIFDate & TimeCalculates the difference between two dates in days, months, or years.
Syntax
=DATEDIF(start_date, end_date, unit)Example
=DATEDIF(A2, TODAY(), "Y")Returns the number of complete years between the date in A2 and today (useful for calculating age).
EDATEDate & TimeReturns a date that is a specified number of months before or after a given date.
Syntax
=EDATE(start_date, months)Example
=EDATE(A2, 3)Returns the date 3 months after the date in A2.
NETWORKDAYSDate & TimeReturns the number of working days (excluding weekends) between two dates.
Syntax
=NETWORKDAYS(start_date, end_date, [holidays])Example
=NETWORKDAYS(A2, B2, E2:E10)Counts business days between A2 and B2, excluding holidays listed in E2:E10.
RANKStatisticalReturns the rank of a number within a list of numbers.
Syntax
=RANK(number, ref, [order])Example
=RANK(A2, A:A, 0)Returns the rank of A2 within column A, with 0 meaning descending order (highest = rank 1).
LARGEStatisticalReturns the k-th largest value from a dataset.
Syntax
=LARGE(array, k)Example
=LARGE(A:A, 3)Returns the 3rd largest value in column A.
UNIQUEStatisticalReturns a list of unique values from a range, removing duplicates. (Dynamic array function)
Syntax
=UNIQUE(array, [by_col], [exactly_once])Example
=UNIQUE(A2:A100)Returns a spilled list of unique values from A2:A100, automatically expanding to fit the results.
FVFinancialCalculates the future value of an investment based on periodic, constant payments and a constant interest rate.
Syntax
=FV(rate, nper, pmt, [pv], [type])Example
=FV(6%/12, 120, -500)Calculates the future value of saving $500/month for 10 years (120 months) at 6% annual interest — the result is what the account will be worth.
IRRFinancialReturns the Internal Rate of Return for a series of cash flows — the discount rate that makes NPV equal to zero.
Syntax
=IRR(values, [guess])Example
=IRR(A2:A7)Calculates the IRR for cash flows in A2:A7, where A2 is typically the initial investment (negative) and A3:A7 are returns.
PMTFinancialCalculates the periodic payment amount for a loan, given interest rate, number of periods, and present value.
Syntax
=PMT(rate, nper, pv, [fv], [type])Example
=PMT(5%/12, 60, -10000)Calculates the monthly payment for a $10,000 loan at 5% annual interest over 60 months (5 years).
NPVFinancialCalculates the Net Present Value of an investment based on a series of future cash flows and a discount rate.
Syntax
=NPV(rate, value1, [value2, ...])Example
=NPV(10%, B2:B6) + A2Calculates NPV at 10% discount rate for cash flows in B2:B6, adding the initial investment in A2 (which is typically negative).
Can't find the formula you need?
Describe what you want in plain English and get the exact formula generated instantly.