Lookup & Reference Functions
VLOOKUP
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Searches for a value in the first column of a range and returns a value in the same row from another column.
Example: =VLOOKUP(A2, $D$2:$F$100, 3, FALSE)
XLOOKUP
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Modern replacement for VLOOKUP. Searches a range or array and returns the corresponding item from a second range or array.
Example: =XLOOKUP(A2, $D$2:$D$100, $F$2:$F$100)
INDEX & MATCH
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Powerful combination for flexible lookups. INDEX returns a value from a specific position, MATCH finds the position.
Example: =INDEX($F$2:$F$100, MATCH(A2, $D$2:$D$100, 0))
OFFSET
=OFFSET(reference, rows, cols, [height], [width])
Returns a reference to a range that is offset from a starting cell or range by a specified number of rows and columns.
Example: =OFFSET(A1, 5, 2, 10, 1)
Financial Functions
NPV
=NPV(rate, value1, [value2], ...)
Calculates the net present value of an investment using a discount rate and series of future cash flows.
Example: =NPV(0.10, B2:B11) + A2
IRR
=IRR(values, [guess])
Returns the internal rate of return for a series of cash flows.
Example: =IRR(B2:B11, 0.1)
XIRR
=XIRR(values, dates, [guess])
Returns the internal rate of return for a schedule of cash flows that are not necessarily periodic.
Example: =XIRR(B2:B11, A2:A11)
PMT
=PMT(rate, nper, pv, [fv], [type])
Calculates the payment for a loan based on constant payments and a constant interest rate.
Example: =PMT(0.05/12, 360, 200000)
FV
=FV(rate, nper, pmt, [pv], [type])
Returns the future value of an investment based on periodic constant payments and a constant interest rate.
Example: =FV(0.06/12, 10*12, -500, -1000, 1)
PV
=PV(rate, nper, pmt, [fv], [type])
Returns the present value of an investment or loan.
Example: =PV(0.08/12, 20*12, 500)
Statistical & Mathematical Functions
SUMIF / SUMIFS
=SUMIF(range, criteria, [sum_range])
=SUMIFS(sum_range, criteria_range1, criteria1, ...)
SUMIF sums based on one condition. SUMIFS sums based on multiple conditions.
Example: =SUMIFS(D:D, A:A, "Revenue", B:B, ">=2024")
COUNTIF / COUNTIFS
=COUNTIF(range, criteria)
=COUNTIFS(criteria_range1, criteria1, ...)
Counts cells that meet one or multiple criteria.
Example: =COUNTIFS(A:A, "Closed", B:B, ">100000")
AVERAGEIF / AVERAGEIFS
=AVERAGEIF(range, criteria, [average_range])
=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)
Calculates average of cells that meet one or multiple criteria.
Example: =AVERAGEIFS(C:C, A:A, "Product A", B:B, ">=2024")
ROUND / ROUNDUP / ROUNDDOWN
=ROUND(number, num_digits)
Rounds a number to a specified number of digits.
Example: =ROUND(A2, 2) or =ROUNDUP(A2, 0)
Logical Functions
IF
=IF(logical_test, value_if_true, value_if_false)
Returns one value if a condition is true and another value if it's false.
Example: =IF(A2>100, "High", "Low")
IFS
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)
Checks multiple conditions and returns a value corresponding to the first TRUE condition.
Example: =IFS(A2>90, "A", A2>80, "B", A2>70, "C", TRUE, "F")
AND / OR
=AND(logical1, [logical2], ...)
=OR(logical1, [logical2], ...)
AND returns TRUE if all arguments are TRUE. OR returns TRUE if any argument is TRUE.
Example: =IF(AND(A2>0, B2>0), "Both Positive", "Not Both Positive")
IFERROR
=IFERROR(value, value_if_error)
Returns a value you specify if a formula evaluates to an error; otherwise returns the result of the formula.
Example: =IFERROR(A2/B2, "Error in calculation")
Date & Time Functions
TODAY / NOW
=TODAY()
=NOW()
TODAY returns current date. NOW returns current date and time.
Example: =TODAY() or =NOW()
DATE
=DATE(year, month, day)
Returns the serial number that represents a particular date.
Example: =DATE(2024, 12, 31)
EOMONTH
=EOMONTH(start_date, months)
Returns the last day of the month that is the indicated number of months before or after start_date.
Example: =EOMONTH(TODAY(), 0)
NETWORKDAYS
=NETWORKDAYS(start_date, end_date, [holidays])
Returns the number of working days between two dates.
Example: =NETWORKDAYS(A2, B2, $H$2:$H$10)
Text Functions
CONCATENATE / CONCAT / TEXTJOIN
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
Joins text from multiple ranges and/or strings with a delimiter.
Example: =TEXTJOIN(", ", TRUE, A2:A5)
LEFT / RIGHT / MID
=LEFT(text, [num_chars])
=RIGHT(text, [num_chars])
=MID(text, start_num, num_chars)
Extract characters from text strings.
Example: =LEFT(A2, 5) or =MID(A2, 3, 10)
TEXT
=TEXT(value, format_text)
Converts a value to text in a specific number format.
Example: =TEXT(TODAY(), "mmmm dd, yyyy")