Essential Formulas and Functions
Formulas vs. Functions
In Excel, a formula is any expression that begins with an equals sign (=). A function is a built-in operation that takes inputs (called arguments) and returns a result. Most analytical formulas combine functions with cell references and operators.
Understanding and memorising the most important functions is one of the fastest ways to increase your analytical output in Excel.
Lookup Functions
Lookup functions retrieve data from a table based on a matching value. They are among the most used functions in analytical work.
VLOOKUP
Looks up a value in the leftmost column of a table and returns a value from a specified column in the same row.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example: Look up the region for a product code:
=VLOOKUP(A2, ProductTable, 3, FALSE)
- A2: the value to look up (product code)
- ProductTable: the named range or range containing the lookup table
- 3: return the value from the 3rd column
- FALSE: exact match (always use FALSE for data lookups)
Limitation: VLOOKUP can only look to the right. The lookup value must be in the leftmost column of the table.
XLOOKUP (Excel 365 and 2021+)
More flexible than VLOOKUP. Can look left or right, returns the first or last match, and has a built-in "if not found" argument.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
Example:
=XLOOKUP(A2, ProductTable[ProductCode], ProductTable[Region], "Not found")
INDEX + MATCH
The classic alternative to VLOOKUP that works in all Excel versions and can look in any direction.
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Example:
=INDEX(ProductTable[Region], MATCH(A2, ProductTable[ProductCode], 0))
Conditional Functions
COUNTIF and COUNTIFS
Count cells that meet one or more conditions.
=COUNTIF(range, criteria)
=COUNTIFS(range1, criteria1, range2, criteria2, ...)
Examples:
=COUNTIF(B:B, "London") -- count rows where region is London
=COUNTIFS(B:B, "London", C:C, ">1000") -- count London rows with value over 1000
SUMIF and SUMIFS
Sum cells that meet one or more conditions.
=SUMIF(range, criteria, sum_range)
=SUMIFS(sum_range, criteria_range1, criteria1, ...)
Examples:
=SUMIF(B:B, "London", D:D) -- total of column D where region is London
=SUMIFS(D:D, B:B, "London", C:C, ">1000") -- London rows over 1000
AVERAGEIF and AVERAGEIFS
Average cells meeting one or more conditions. Same syntax as SUMIF/SUMIFS.
Logical Functions
IF
Returns one value if a condition is true, another if false.
=IF(logical_test, value_if_true, value_if_false)
Example:
=IF(D2>1000, "High", "Low") -- categorise by value
=IF(D2="", "Missing", D2) -- handle blanks
IFS (Multiple conditions)
=IFS(test1, value1, test2, value2, ...)
AND and OR
Combine multiple conditions.
=IF(AND(B2="London", C2>500), "Target", "Other")
=IF(OR(B2="London", B2="Manchester"), "North England", "Other")
IFERROR
Handle formula errors gracefully.
=IFERROR(VLOOKUP(A2, Table, 2, FALSE), "Not found")
Text Functions
Essential for cleaning messy text data:
| Function | Purpose | Example |
|---|---|---|
| TRIM | Remove extra spaces | =TRIM(A2) |
| UPPER / LOWER | Change case | =UPPER(A2) |
| LEFT / RIGHT | Extract characters from start/end | =LEFT(A2, 5) |
| MID | Extract characters from middle | =MID(A2, 3, 4) |
| LEN | Count characters | =LEN(A2) |
| FIND / SEARCH | Find position of text | =FIND("@", A2) |
| SUBSTITUTE | Replace text | =SUBSTITUTE(A2, "-", "") |
| CONCATENATE or & | Combine text | =A2&" "&B2 |
| TEXT | Format number as text | =TEXT(D2, "DD/MM/YYYY") |
Date Functions
| Function | Purpose | Example |
|---|---|---|
| TODAY() | Current date | =TODAY() |
| NOW() | Current date and time | =NOW() |
| YEAR / MONTH / DAY | Extract date parts | =YEAR(A2) |
| DATEDIF | Calculate date difference | =DATEDIF(A2, B2, "M") -- months |
| EDATE | Add months to a date | =EDATE(A2, 3) |
| EOMONTH | End of month | =EOMONTH(A2, 0) |
| WEEKDAY | Day of week number | =WEEKDAY(A2, 2) -- 1=Monday |
| TEXT with dates | Format date as text | =TEXT(A2, "MMMM YYYY") |
Statistical Functions
| Function | Purpose |
|---|---|
| SUM | Total |
| AVERAGE | Mean |
| MEDIAN | Middle value |
| MIN / MAX | Smallest / largest |
| COUNT | Count numbers |
| COUNTA | Count non-empty cells |
| STDEV | Standard deviation |
| LARGE / SMALL | Nth largest or smallest |
| PERCENTILE | Value at a given percentile |
Practical Formula Patterns
Running total
=SUM($D$2:D2) -- absolute start, relative end
Percentage of total
=D2/SUM($D$2:$D$100)
Year-on-year growth
=(D2-D1)/D1
Rank within group
=COUNTIFS($B$2:$B$100, B2, $D$2:$D$100, ">"&D2)+1
Key Takeaways
- XLOOKUP (Excel 365+) is the modern standard for lookups, replacing VLOOKUP. For older Excel versions, use INDEX + MATCH for full flexibility.
- SUMIFS and COUNTIFS are the workhorses of conditional analysis -- master these to answer most segmentation questions in business data.
- Logical functions (IF, IFS, AND, OR, IFERROR) allow you to categorise, flag, and clean data programmatically.
- Text functions (TRIM, SUBSTITUTE, LEFT, MID, FIND) are essential for cleaning raw data from business systems, which is often messy.
- Date functions (YEAR, MONTH, DATEDIF, EOMONTH) enable time-based analysis including period comparisons and trend identification.
Practice Exercise
Using a sales dataset with columns Date, Product, Region, Units, Price:
- Use SUMIFS to calculate total revenue per region
- Use COUNTIFS to count the number of transactions per product per month
- Use XLOOKUP or INDEX+MATCH to pull a product category from a separate lookup table
- Use IF with AND to flag transactions where Units > 100 AND Region is "North"
- Use DATEDIF to calculate how many days have elapsed since each transaction date
Try it yourself
Key Takeaways
- XLOOKUP is the modern standard for lookups in Excel 365+; use INDEX + MATCH for older versions as it is more flexible than VLOOKUP.
- SUMIFS and COUNTIFS are core analytical functions for segmenting and aggregating data by multiple criteria.
- Wrap error-prone formulas in IFERROR to handle no-match results gracefully instead of displaying error codes.
- Text functions (TRIM, SUBSTITUTE, LEFT, MID, FIND) are essential for cleaning messy imported data before analysis.
- Date functions (DATEDIF, EOMONTH, TEXT) enable time-based calculations, period comparisons, and date formatting.
Quick Quiz
1.What is the primary advantage of XLOOKUP over VLOOKUP?
2.You want to sum sales values in column D only where column B contains 'North' AND column C contains a value greater than 500. Which formula is correct?
3.Which text function is most useful for cleaning imported data that contains inconsistent spacing?
4.What does =IFERROR(VLOOKUP(A2, Table, 2, FALSE), "Not found") return when the lookup value is not in the table?
Ready to go further?
CareerEx gives you structured 12-week training, live classes every Saturday and Sunday, real tutor feedback, and a certificate. Join the next cohort.
Join CareerEx