Lookup Functions: VLOOKUP, HLOOKUP, and XLOOKUP
Lookup functions are the backbone of any Excel model that combines data from multiple sources. VLOOKUP (Vertical Lookup) searches the first column of a range and returns a value from the same row in a specified column. Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The most common VLOOKUP mistake is forgetting to set range_lookup to FALSE for an exact match. XLOOKUP (Excel 365/2021) replaces both VLOOKUP and HLOOKUP with a more flexible function: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]). XLOOKUP can search left-to-right or right-to-left, handles errors natively, and is significantly easier to maintain in large models.
- =VLOOKUP(A2,Products!$A:$D,3,FALSE) — find product price in the Products table
- =XLOOKUP(A2,Products!$A:$A,Products!$C:$C,"Not found") — same result, more readable
- Use INDEX/MATCH for compatibility with pre-2021 Excel versions
Conditional Functions: IF, IFS, SUMIF, COUNTIF
Conditional functions perform calculations based on criteria. IF is the most fundamental: =IF(logical_test, value_if_true, value_if_false). Nested IFs handle multiple conditions but become unreadable beyond 2–3 levels — use IFS instead: =IFS(condition1, result1, condition2, result2, ...). SUMIF sums values that meet a criterion: =SUMIF(range, criteria, sum_range). SUMIFS adds multiple criteria: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2). COUNTIF counts cells meeting a condition; COUNTIFS handles multiple conditions. These six functions cover the vast majority of conditional business calculations.
- =SUMIF(A:A,"East",B:B) — sum all East region sales
- =COUNTIFS(C:C,"Q1",D:D,">1000") — count Q1 transactions over $1,000
- =IFS(score>=90,"A",score>=80,"B",score>=70,"C",TRUE,"F") — grade calculator
Text Functions for Data Cleaning
Messy data is the reality of most business Excel work. Text functions clean and standardize string data imported from databases or external systems. TRIM removes extra spaces. CLEAN removes non-printable characters. UPPER/LOWER/PROPER standardize case. LEFT, RIGHT, and MID extract substrings. FIND and SEARCH locate characters within strings. CONCATENATE (or &) and TEXTJOIN combine strings. In Excel 365, TEXTSPLIT splits a string into an array using a delimiter — useful for parsing combined fields.
- =TRIM(A2) — remove leading, trailing, and duplicate spaces
- =PROPER(A2) — convert "john smith" to "John Smith"
- =LEFT(A2,FIND(" ",A2)-1) — extract first name from full name
- =TEXTJOIN(", ",TRUE,B2:B10) — combine a range of cells with comma separation
Date and Time Functions
Date calculations are common in project tracking, financial modeling, and reporting. TODAY() returns the current date. NOW() returns the current date and time. DATEDIF calculates the difference between dates in days, months, or years. NETWORKDAYS calculates working days between two dates (excluding weekends and optionally holidays). EDATE returns a date a specified number of months from a start date — useful for calculating contract end dates. EOMONTH returns the last day of the month a specified number of months in the future, commonly used in financial models for period end dates.
- =DATEDIF(start_date,end_date,"d") — number of days between dates
- =NETWORKDAYS(A2,B2,Holidays!$A:$A) — business days excluding holidays
- =EOMONTH(TODAY(),0) — last day of the current month
PivotTables: The Most Powerful Excel Feature
PivotTables transform large datasets into dynamic summary views without writing a single formula. A PivotTable can summarize thousands of rows into a compact cross-tabulation in seconds. To create one, select your data range and use Insert → PivotTable. Drag fields to the Rows, Columns, Values, and Filters areas. PivotTables are particularly powerful when combined with PivotCharts, slicers (interactive filters), and calculated fields that extend the analysis beyond the raw data. Refresh the PivotTable when underlying data changes with right-click → Refresh. Use Ctrl+Alt+F5 to refresh all PivotTables in a workbook.
Dynamic Array Functions (Excel 365)
Excel 365 introduced dynamic array functions that spill results into a range of cells automatically. FILTER returns rows that meet criteria: =FILTER(A2:C100,B2:B100="East"). SORT and SORTBY sort arrays dynamically. UNIQUE returns distinct values from a range. SEQUENCE generates a series of numbers. These functions replace many complex ARRAY formulas (entered with Ctrl+Shift+Enter in older Excel) with simpler, more readable alternatives. If you are using Excel 365, replace old array workarounds with these dynamic functions whenever possible — they are faster, easier to maintain, and produce more reliable results.
- =FILTER(A2:C100,C2:C100>1000) — return all rows where column C > 1000
- =SORT(UNIQUE(B2:B100)) — sorted list of unique values
- =SEQUENCE(12,1,DATE(2025,1,1),30) — 12 dates 30 days apart