Excel practice › Excel functions
Excel functions, with examples you can edit
Each page explains one function the way you'd use it at work: what it returns, its arguments, the mistakes that produce errors. Every example is a working sheet, so you can change the formula and watch the result, then solve a few exercises. Answers are included.
Lookup
- VLOOKUPFind a value in the first column of a table and return something from the same row.
- XLOOKUPFind a value in one column and return the matching value from another, in any direction.
- INDEX MATCHTwo functions used as one lookup: MATCH finds the position, INDEX returns the value. Left and two-way lookups.
Logic
- IFReturn one value when a condition is true and another when it is false. Text, numbers, nested IF.
- AND, OR, NOTCombine conditions: AND needs all of them true, OR needs one, NOT reverses. Mostly used inside IF.
- IFERROR and IFNAReplace an error with a value you choose. IFERROR catches every error, IFNA only #N/A.
Conditional math
- SUMIFAdd up the numbers on the rows that meet one condition.
- SUMIFSAdd up the numbers on the rows that meet several conditions, such as a region and a date range.
- COUNTIFCount the cells that meet one condition: a text, a number comparison, a wildcard pattern.
- COUNTIFSCount the rows that meet two or more conditions at the same time.
- AVERAGEIF and AVERAGEIFSAverage only the rows that meet one condition (AVERAGEIF) or several (AVERAGEIFS).
Text
- LEFT, RIGHT, MIDTake characters from the start, the end or the middle of a text, like one part of a product code.
- TRIMRemove the extra spaces that make two cells look equal when they aren't.
- CONCAT and &Join text from several cells into one, with the separators you choose.
- TEXTTurn a number or a date into text written the way a format code says: $1,250.00, Mar 2026, 12%.
Dates
- EOMONTHThe last day of a month, any number of months before or after a date. Add 1 for the first day of the next.
- DATEDIFComplete years, months or days between two dates, and the unit Microsoft says to avoid.
- NETWORKDAYSWorking days between two dates, both ends counted, weekends out, holidays out if you list them.
Dynamic arrays
Basics
- ROUNDRound a number to a set number of digits. Unlike formatting, it changes the value itself.