Formuladrill

Excel functions reference

Every Excel function people search for, with the direct answer first, the syntax, worked examples with their results, the mistakes that produce #N/A and #VALUE!, and the drills on Formuladrill that practice it on a live sheet.

40 functions, each with the direct answer, syntax, worked examples, the mistakes people make and the drills that practice it. Updated November 2026.

Lookup

  • VLOOKUPHow to use VLOOKUP in Excel to find a value in the first column of a table and return a value from another column, with exact and approximate match examples.
  • XLOOKUPHow XLOOKUP works in Excel: find a value in one column and return the match from another, in any direction, with a not found value and next-smaller match.
  • INDEX MATCHHow to combine INDEX and MATCH in Excel to look up a value from any column, left or right, with worked examples, a two-way lookup and the usual errors.
  • MATCHHow the Excel MATCH function returns the position of a value in a row or column, with exact and approximate match types, examples and the causes of #N/A.
  • INDEXHow the Excel INDEX function returns the cell at a given row and column of a range, with examples for a single column, a grid and the pairing with MATCH.
  • CHOOSEHow the Excel CHOOSE function returns one of several values based on an index number, with examples for weekday names, quarters and scenario switches.

Math and statistics

  • SUMIFHow to use SUMIF in Excel to add up numbers that match one criterion, with examples for text, greater than, cell references and wildcards, and common fixes.
  • SUMIFSHow to use SUMIFS in Excel to add values that meet two or more conditions, with examples for region and status, date ranges, not-equal and wildcards.
  • COUNTIFHow to use COUNTIF in Excel to count cells equal to a value, above a number, containing text or not blank, with examples, a duplicate check and fixes.
  • COUNTIFSHow to use COUNTIFS in Excel to count rows where two or more conditions are all true, with examples for text plus number, date ranges and not-equal.
  • SUMPRODUCTHow the Excel SUMPRODUCT function multiplies ranges and sums the result, with examples for quantity times price, weighted average and sums with conditions.
  • AVERAGEIFHow to use AVERAGEIF and AVERAGEIFS in Excel to average only the cells that match a condition, with examples for one region, positive values and two criteria.
  • MAXIFS and MINIFSHow to use MAXIFS and MINIFS in Excel to find the highest or lowest value that meets conditions, with examples by region, status and date, plus the 2016 way.
  • COUNT, COUNTA and COUNTBLANKHow COUNT, COUNTA and COUNTBLANK differ in Excel: COUNT counts numbers, COUNTA any non-empty cell, COUNTBLANK empty cells, with examples and the blank trap.
  • ROUNDHow to round in Excel with ROUND, ROUNDUP, ROUNDDOWN and MROUND: to 2 decimals, to the nearest hundred and nearest 5, and why formatting is not rounding.
  • ABSHow the Excel ABS function returns a number without its sign, with examples for the gap between two cells, a tolerance check and making negatives positive.
  • MODHow the Excel MOD function returns the remainder of a division, with examples for odd and even, every nth row, the decimal part of a number and the sign rule.
  • RANKHow to rank values in Excel with RANK.EQ and RANK.AVG, descending or ascending, with examples, how ties work, a tie-breaker and LARGE for the nth value.

Logical

  • IFHow the Excel IF function returns one value when a test is true and another when false, with pass or fail, blank cell, nested IF and IF with AND examples.
  • IFSHow the Excel IFS function checks several conditions in order and returns the first match, with grade and shipping tier examples and the fix for #N/A.
  • IFERRORHow the Excel IFERROR function returns a value you choose instead of an error, with examples for division by zero and VLOOKUP, and when IFNA is better.
  • AND, OR, NOTHow the Excel AND, OR and NOT functions test several conditions and return TRUE or FALSE, with examples inside IF, a between test and a not blank test.
  • ISERROR, ISBLANK and IS functionsHow the Excel IS functions (ISBLANK, ISERROR, ISNA, ISNUMBER, ISTEXT) return TRUE or FALSE about a cell, with examples inside IF and the empty string trap.

Text

  • TEXTHow the Excel TEXT function turns a number or date into text in a format you choose, with examples for dates, thousand separators, percentages and padding.
  • TEXTJOINHow the Excel TEXTJOIN function joins a range into one string with a separator and skips empty cells, with examples for comma lists and a conditional join.
  • LEN, LEFT, RIGHT, MIDHow to pull characters from the start, end or middle of a cell with LEFT, RIGHT and MID, measure length with LEN, and use FIND to split text at a character.
  • TRIMHow the Excel TRIM function removes leading, trailing and repeated spaces, why lookups fail without it, and how to remove the non-breaking spaces it leaves.
  • PROPER, UPPER, LOWERHow to change the case of text in Excel with PROPER, UPPER and LOWER, with examples for cleaning names and emails, TRIM, and the names PROPER gets wrong.
  • SUBSTITUTEHow the Excel SUBSTITUTE function replaces one piece of text with another, with examples for removing dashes, swapping the nth occurrence and counting commas.
  • CONCAT and &How to join text in Excel with CONCAT, CONCATENATE and the & operator, with examples for full names, email addresses, numbered IDs and a formatted date.

Dates

  • DATE and DATEDIFHow the Excel DATE function builds a date from year, month and day, and DATEDIF counts complete years, months or days between two dates, plus an age formula.
  • EOMONTH and EDATEHow the Excel EOMONTH function returns the last day of a month and EDATE adds or subtracts whole months, with examples for payment terms and first of month.
  • NETWORKDAYS and WORKDAYHow NETWORKDAYS counts the working days between two dates and WORKDAY adds working days, skipping weekends and holidays, and .INTL for other weekends.
  • TODAY and NOWHow the Excel TODAY and NOW functions insert the current date or date and time, with formulas for days until a deadline, age, overdue flags and fixed dates.

Financial

  • PMTHow the Excel PMT function calculates the payment on a loan or the deposit for a savings goal, with mortgage and car loan examples and the monthly rate rule.
  • NPVHow the Excel NPV function discounts future cash flows at a rate, why the initial investment is added outside the function, with a worked example and XNPV.
  • IRRHow the Excel IRR function finds the rate at which cash flows net to zero, with a worked example, the cash flow layout it needs, the #NUM! fix and XIRR.

Dynamic arrays

  • UNIQUEHow the Excel UNIQUE function returns a list without duplicates that spills down the sheet, with examples for a distinct list, a distinct count and a sort.
  • FILTERHow the Excel FILTER function returns all the rows of a range that meet a condition, with examples for one criterion, AND and OR, a not found value and SORT.
  • SORT and SORTBYHow the Excel SORT and SORTBY functions sort a range with a formula that updates itself, with examples for descending order, several columns and outside keys.

Reading is not practicing

Every function here links to drills that check your answer on live data. All of them are free to try.

Start practicing