HLOOKUP Function in Excel: Look Up Across the Top Row of a Table
HLOOKUP is VLOOKUP turned sideways: it searches the top row and returns a value from a row below.
Read article →Excel
Excel formulas, unusual formula combinations, VBA, Power Query and dashboards — from basics to clever tricks.
FILTER Function in Excel: Return Every Row That Matches
FILTER Function in Excel: Return Every Row That Matches
FILTER returns all rows (or columns) that meet a condition, and the result updates live as data changes.
Read article →Percentage of Total and Percentage Change in Excel (Without #DIV/0! Errors)
Percentage of Total and Percentage Change in Excel (Without #DIV/0! Errors)
Share of total, growth vs last month, contribution by region and year-on-year change — the correct formulas, absolute references, handling zero and blank values,…
Read article →HSTACK Function in Excel: Put Arrays Side by Side
HSTACK Function in Excel: Put Arrays Side by Side
HSTACK joins arrays horizontally — build a small report from separate results in one formula.
Read article →XLOOKUP Function in Excel: The Modern Lookup That Replaces VLOOKUP
XLOOKUP Function in Excel: The Modern Lookup That Replaces VLOOKUP
XLOOKUP finds a value in one range and returns the matching item from another. It looks in any direction, defaults to exact match, and…
Read article →FORECAST Function in Excel: Straight-Line Forecast (Older Name)
FORECAST Function in Excel: Straight-Line Forecast (Older Name)
FORECAST predicts a value on a straight-line trend. It is a compatibility function: Excel keeps it so workbooks from Excel 2007 and earlier still…
Read article →VLOOKUP Function in Excel: Look Up a Value in the First Column of a Table
VLOOKUP Function in Excel: Look Up a Value in the First Column of a Table
VLOOKUP finds a value in the first column of a table and returns a value from another column in the same row.
Read article →The Complete Guide to Excel Lookups: VLOOKUP, XLOOKUP, INDEX-MATCH and Beyond
The Complete Guide to Excel Lookups: VLOOKUP, XLOOKUP, INDEX-MATCH and Beyond
Everything about looking up data in Excel in one place: exact and approximate matches, left lookups, two-way lookups, multiple criteria, last match, returning all…
Read article →Monthly Totals From Daily Data in Excel (SUMIFS + EOMONTH, Pivot and GROUPBY)
Monthly Totals From Daily Data in Excel (SUMIFS + EOMONTH, Pivot and GROUPBY)
Turn a daily sales list into month-wise totals: SUMIFS with EOMONTH date bounds, a month-by-region grid, TEXT month keys, pivot grouping and the new…
Read article →Excel LAMBDA Lesson 8: Package and Share Your Function Library
Excel LAMBDA Lesson 8: Package and Share Your Function Library
Collect your LAMBDA functions in one library workbook, copy them to other files, use the Advanced Formula Environment and keep versions under control.
Read article →Excel LAMBDA Lesson 7: Optional Arguments and Error Handling
Excel LAMBDA Lesson 7: Optional Arguments and Error Handling
Make LAMBDA functions friendly: optional parameters with ISOMITTED, sensible defaults, SAFEDIV and clear messages for bad input.
Read article →Excel LAMBDA Lesson 6: MAP, BYROW, BYCOL and MAKEARRAY
Excel LAMBDA Lesson 6: MAP, BYROW, BYCOL and MAKEARRAY
Apply your functions across whole ranges: MAP for every cell, BYROW and BYCOL for row and column totals, MAKEARRAY to generate grids.
Read article →