Excel Advanced Formulas 2026

=INDEX(C3:E9, MATCH(B13,C3:C9,0), MATCH(B14,C3:E3,0))

📝 John → Sales → Q1 = $45,200
✅ Looks left/right/up/down (VLOOKUP can't)

=XLOOKUP("John", A:A, B:B)                    → Salary
=XLOOKUP(lookup, lookup_array, return_array, "Not found", 0)
=XLOOKUP(A1, products, prices, 0)              → Exact match
=XLOOKUP(B1, dates, sales, , -1)               → Last match

=SUMIFS(sales, region,"North", year,2026, product,"Widget")
=SUMIFS(amounts, status,"Paid", date,">1/1/2026", date,"<4/1/2026")

📊 North Widgets 2026 = $1.2M

=FILTER(A:C, (B:B>100)*(C:C="Active"))       → Active customers
=SORT(FILTER(sales, region="North"), 2, -1)    → Top North sales
=UNIQUE(customers)                             → Remove duplicates
=SEQUENCE(10,1,1,1)                           → 1,2,3...10

=LAMBDA(x,y, x*y^2)(5,3)                        → 45

💾 Name Manager → PROFIT = LAMBDA(sales,cost, sales-cost)
=PROFIT(1000,700)                              → $300

=LET(sales,1000, cost,700, profit,sales-cost, profit*0.3) → Tax

=TEXTSPLIT(A1,",;|")                           → Split delimiters
=SUM(OFFSET(A1,0,0,E2,1))                      → Dynamic sum
=SUMPRODUCT((A:A>100)*(B:B>50))                → Multi-condition

// Sales by Region+Month
=SUMIFS(sales, region,A2, month,B2)

// Top 5 customers
=SORT(FILTER(customers,(sales>10000)),sales,-1)

// Employee bonus
=IF(sales>target, sales*0.1, 0)

// Aging report
=DATEDIF(date,TODAY(),"d")

Formula          | Use Case                    | Example
-----------------|----------------------------|--------------------------------
INDEX/MATCH      | 2D lookup                  | =INDEX(data,MATCH(row,col))
XLOOKUP          | Error-proof lookup         | =XLOOKUP(key,lookup,return)
SUMIFS           | Multi-criteria sum         | =SUMIFS(sum,cond1,crit1,...)
FILTER           | Dynamic table              | =FILTER(data,condition)
LAMBDA           | Custom function            | =LAMBDA(x,y,x*y^2)(5,3)
LET              | Variable reuse             | =LET(x,10,x^2)
TEXTSPLIT        | Split text                 | =TEXTSPLIT(A1,",")
OFFSET+SUM       | Dynamic range              | =SUM(OFFSET(A1,0,0,n,1))
SUMPRODUCT       | Array math                 | =SUMPRODUCT(A1:A10,B1:B10)
IFERROR          | Error handling             | =IFERROR(formula,0)
AGGREGATE        | Ignore errors/hidden       | =AGGREGATE(14,6,A1:A10)

1. North Widgets Q1 sales
2. Top 5 customers
3. Bonus > target
4. Region ranking table
5. Dynamic pivot alternative