Excel Advanced Formulas for Data Analysts — XLOOKUP, Dynamic Arrays, SUMIFS, Power Formulas & More

=XLOOKUP(A2, Products[ProductID], Products[ProductName])
-- Returns the product name for the ID in A2
-- No column number required — you specify the exact return column

XLOOKUP(A2, Products[ProductID], Products[Price], "Not found")
-- 4th argument: what to return if not found (replaces IFERROR(VLOOKUP(...)))

XLOOKUP(A2, Products[ProductID], Products[Price], 0, -1)
-- match_mode -1 = exact match or next smaller value
-- match_mode 1  = exact match or next larger value
-- Useful for tiered pricing lookups

XLOOKUP(A2, B2:B1000, C2:E1000)
-- Return multiple columns at once (C, D, and E)
-- VLOOKUP cannot do this

=-- One-way lookup (equivalent to XLOOKUP)
INDEX(Products[Price], MATCH(A2, Products[ProductID], 0))

-- Two-way lookup: find price at intersection of product row + month column
INDEX(PriceMatrix, MATCH(A2, RowHeaders, 0), MATCH(B2, ColHeaders, 0))

-- Look LEFT (impossible with VLOOKUP)
INDEX(Products[ProductCode], MATCH(A2, Products[ProductName], 0))
-- Looks up by name (right column), returns code (left column)

=-- SUMIFS: sum revenue where channel=Online AND region=North
SUMIFS(Sales[Revenue], Sales[Channel], "Online", Sales[Region], "North")

-- With wildcards: all cities containing "Delhi"
SUMIFS(Sales[Revenue], Sales[City], "*Delhi*")

-- With date range: revenue between two dates
SUMIFS(Sales[Revenue],
    Sales[OrderDate], ">="&DATE(2026,1,1),
    Sales[OrderDate], "<="&DATE(2026,3,31))

-- With cell references (makes formula updatable from a dropdown)
SUMIFS(Sales[Revenue],
    Sales[Channel], B1,
    Sales[Region],  B2,
    Sales[OrderDate], ">="&B3)

-- COUNTIFS: count orders where status=Completed AND revenue>10000
COUNTIFS(Sales[Status], "Completed", Sales[Revenue], ">"&10000)

-- AVERAGEIFS: average order value for Online channel only
AVERAGEIFS(Sales[Revenue], Sales[Channel], "Online")

-- MAXIFS / MINIFS (Excel 2019+): max revenue per region
MAXIFS(Sales[Revenue], Sales[Region], "North")

=-- All orders where Channel is "Online"
FILTER(Sales, Sales[Channel]="Online")

-- Multiple conditions (AND: multiply; OR: add)
FILTER(Sales, (Sales[Channel]="Online") * (Sales[Revenue]>10000))
-- Both must be true (* = AND)

FILTER(Sales, (Sales[Region]="North") + (Sales[Region]="South"))
-- Either can be true (+ = OR)

-- Return only specific columns from a filtered result
FILTER(Sales[Revenue], Sales[Channel]="Online")

-- With IFERROR for "no results" case
IFERROR(FILTER(Sales, Sales[Status]="Pending"), "No pending orders")

=-- Sort a range by column 3 descending
SORT(Sales, 3, -1)        -- 3=third column, -1=descending

-- Sort a table by Revenue column descending
SORT(SalesTable, MATCH("Revenue", SalesTable[#Headers], 0), -1)

-- SORTBY: sort by an external array (not a column in the range)
SORTBY(Sales, Sales[Revenue], -1)  -- cleaner when using table references

-- Chain: filter Online orders, sort by revenue descending
SORT(FILTER(Sales, Sales[Channel]="Online"), 4, -1)

=-- All unique regions in the data
UNIQUE(Sales[Region])

-- Unique combinations of two columns
UNIQUE(Sales[Region]:Sales[Channel])

-- Values that appear exactly once (appear_once=TRUE)
UNIQUE(Sales[CustomerID], FALSE, TRUE)

-- Combine with SORT for a sorted unique list (perfect for dropdown source)
SORT(UNIQUE(Sales[Region]))

=-- Numbers 1 to 12 (for month labels)
SEQUENCE(12)

-- 2D array: 4 rows × 3 columns starting at 1, step 1
SEQUENCE(4, 3, 1, 1)

-- Generate a date series (12 month-starts from Jan 2026)
DATE(2026, SEQUENCE(12), 1)

-- Combine with TEXT for month name labels
TEXT(DATE(2026, SEQUENCE(12), 1), "mmm yyyy")

=-- Define in Name Manager (Formulas → Name Manager → New):
-- Name: GrossMargin
-- Refers to:
LAMBDA(revenue, cost, (revenue - cost) / revenue)

-- Then use anywhere in the workbook:
GrossMargin(B2, C2)        -- returns (B2-C2)/B2
GrossMargin(10000, 6000)   -- returns 0.4

-- LAMBDA with MAP (apply to each row in a column)
MAP(Sales[Revenue], Sales[COGS],
    LAMBDA(rev, cost, (rev - cost) / rev))
-- Returns a column of margin % for every row — no ARRAYFORMULA needed

-- LAMBDA for tiered commission (replaces nested IFs)
-- Name: Commission
LAMBDA(revenue,
    IFS(
        revenue >= 1000000, revenue * 0.1,
        revenue >= 500000,  revenue * 0.07,
        revenue >= 100000,  revenue * 0.05,
        TRUE,               revenue * 0.03
    )
)

=-- IFERROR: return a fallback when a formula errors
IFERROR(XLOOKUP(A2, Products[ID], Products[Price]), 0)
IFERROR(A2/B2, "")           -- blank instead of #DIV/0!

-- IFNA: only catches #N/A errors (more precise than IFERROR)
IFNA(XLOOKUP(A2, LookupRange, ReturnRange), "Not found")

-- LET: name intermediate results (like variables)
LET(
    revenue,  B2,
    cost,     C2,
    margin,   revenue - cost,
    margin / revenue          -- final expression returned
)
-- Use LET to avoid repeating expensive subformulas

-- DATE: construct a date from parts
DATE(2026, 10, 1)             -- 1 Oct 2026
DATE(YEAR(A2), MONTH(A2), 1)  -- first day of the same month as A2

-- EOMONTH: last day of a month
EOMONTH(A2, 0)    -- last day of A2's month
EOMONTH(A2, 1)    -- last day of NEXT month

-- EDATE: same day N months forward/backward
EDATE(A2, 3)      -- 3 months after A2
EDATE(A2, -1)     -- 1 month before A2

-- DATEDIF: difference in days, months, or years
DATEDIF(A2, B2, "D")   -- days between A2 and B2
DATEDIF(A2, B2, "M")   -- complete months
DATEDIF(A2, B2, "Y")   -- complete years (for age calculation)

-- NETWORKDAYS: working days between two dates
NETWORKDAYS(A2, B2)
NETWORKDAYS(A2, B2, Holidays)  -- exclude holidays in a named range

-- WEEKNUM / ISOWEEKNUM: week number of year
ISOWEEKNUM(A2)   -- ISO week number (Monday start, common in reporting)