# XLSX Formula Examples

Production-ready formula examples for common business scenarios.

## Lookup Scenarios

### Find Product Price from Catalog

Data layout: Product names in column A, prices in column B, lookup value in E1.

Modern approach:
```
=XLOOKUP(E1, A:A, B:B, "Not Found", 0)
```

Legacy approach:
```
=IFERROR(VLOOKUP(E1, A:B, 2, FALSE), "Not Found")
```

Flexible approach (works left or right):
```
=IFERROR(INDEX(B:B, MATCH(E1, A:A, 0)), "Not Found")
```

### Two-Way Lookup (Row and Column)

Data layout: Row headers in column A, column headers in row 1, data in B2:D10.

```
=INDEX(B2:D10, MATCH(G1, A2:A10, 0), MATCH(G2, B1:D1, 0))
```

With error handling:
```
=IFERROR(INDEX(B2:D10, MATCH(G1, A2:A10, 0), MATCH(G2, B1:D1, 0)), "Not Found")
```

### Multiple Criteria Lookup

Find value where Region="East" AND Product="Widget".

Using SUMIFS (if result is numeric and unique):
```
=SUMIFS(C:C, A:A, "East", B:B, "Widget")
```

Using INDEX/MATCH with array:
```
=INDEX(C:C, MATCH(1, (A:A="East")*(B:B="Widget"), 0))
```

Using FILTER (Excel 365+):
```
=FILTER(C:C, (A:A="East")*(B:B="Widget"), "Not Found")
```

### Return Multiple Columns

Get all data for a matching ID.

```
=XLOOKUP(E1, A:A, B:D)
```

This returns columns B, C, and D for the matching row.

### Approximate Match (Price Tiers)

Find applicable tier based on quantity. Tiers in A:A (ascending), discounts in B:B.

```
=XLOOKUP(E1, A:A, B:B, , -1)
```

Legacy:
```
=VLOOKUP(E1, A:B, 2, TRUE)
```

Ensure tier values are sorted ascending.

## Conditional Calculations

### Grade Assignment

Score in A1, assign letter grade.

Using IFS:
```
=IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", A1>=60, "D", TRUE, "F")
```

Using nested IF:
```
=IF(A1>=90, "A", IF(A1>=80, "B", IF(A1>=70, "C", IF(A1>=60, "D", "F"))))
```

### Status Based on Multiple Conditions

Project status based on completion and due date.

```
=IF(AND(B1=100, A1<=TODAY()), "Complete",
 IF(AND(B1<100, A1<TODAY()), "Overdue",
 IF(B1=100, "Complete Early", "In Progress")))
```

### Commission Calculation with Tiers

Sales in A1, tiered commission rates.

```
=IF(A1>=100000, A1*0.15,
 IF(A1>=50000, A1*0.10,
 IF(A1>=25000, A1*0.07, A1*0.05)))
```

Using formula with brackets:
```
=A1*IFS(A1>=100000, 0.15, A1>=50000, 0.10, A1>=25000, 0.07, TRUE, 0.05)
```

### Bonus Eligibility

Multiple criteria: performance rating AND years of service.

```
=IF(AND(A1>=4, B1>=2), "Eligible",
 IF(OR(A1>=5, B1>=5), "Review", "Not Eligible"))
```

## Aggregation with Conditions

### Sum by Category

Sum sales where category is "Electronics".

```
=SUMIF(A:A, "Electronics", B:B)
```

### Sum with Multiple Criteria

Sum sales where Region="East" AND Month="January".

```
=SUMIFS(C:C, A:A, "East", B:B, "January")
```

### Sum with Wildcards

Sum sales for products starting with "Pro".

```
=SUMIF(A:A, "Pro*", B:B)
```

### Sum Excluding Certain Values

Sum all except "Returns".

```
=SUMIF(A:A, "<>Returns", B:B)
```

### Count Unique Values

Count distinct entries in column A.

Modern (Excel 365+):
```
=COUNTA(UNIQUE(A2:A100))
```

Legacy:
```
=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))
```

### Running Total

Running sum of column B, starting from row 2.

In cell C2:
```
=SUM($B$2:B2)
```

Copy down to create running total.

### Conditional Average Excluding Zeros

Average column B where values > 0.

```
=AVERAGEIF(B:B, ">0")
```

### Weighted Average

Values in A:A, weights in B:B.

```
=SUMPRODUCT(A2:A100, B2:B100)/SUM(B2:B100)
```

## Date Calculations

### Age Calculation

Birth date in A1.

```
=DATEDIF(A1, TODAY(), "Y")
```

Full age with months:
```
=DATEDIF(A1, TODAY(), "Y") & " years, " & DATEDIF(A1, TODAY(), "YM") & " months"
```

### Days Until Due Date

Due date in A1.

```
=A1-TODAY()
```

With status:
```
=IF(A1-TODAY()<0, "Overdue", IF(A1-TODAY()<=7, "Due Soon", "On Track"))
```

### Business Days Between Dates

Start in A1, end in B1, holidays in D:D.

```
=NETWORKDAYS(A1, B1, D:D)
```

### Add Business Days

Start date in A1, add 10 business days, holidays in D:D.

```
=WORKDAY(A1, 10, D:D)
```

### First/Last Day of Month

Date in A1.

First day:
```
=DATE(YEAR(A1), MONTH(A1), 1)
```

Last day:
```
=EOMONTH(A1, 0)
```

### Quarter from Date

Date in A1.

```
=ROUNDUP(MONTH(A1)/3, 0)
```

Or:
```
="Q" & ROUNDUP(MONTH(A1)/3, 0)
```

### Fiscal Year (Starting July)

Date in A1, fiscal year starts July 1.

```
=IF(MONTH(A1)>=7, YEAR(A1)+1, YEAR(A1))
```

## Text Manipulation

### Extract First/Last Name

Full name in A1 (format: "First Last").

First name:
```
=LEFT(A1, FIND(" ", A1)-1)
```

Last name:
```
=RIGHT(A1, LEN(A1)-FIND(" ", A1))
```

### Extract Domain from Email

Email in A1.

```
=MID(A1, FIND("@", A1)+1, LEN(A1))
```

### Clean Phone Number (Remove Non-Digits)

Phone in A1 with formatting like "(123) 456-7890".

Remove common characters:
```
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "(", ""), ")", ""), "-", ""), " ", "")
```

### Proper Case with Exceptions

Name in A1, handle "McDonald", "O'Brien".

```
=PROPER(A1)
```

Note: Manual correction needed for special cases.

### Pad Number with Leading Zeros

Number in A1, pad to 5 digits.

```
=TEXT(A1, "00000")
```

Or:
```
=RIGHT("00000" & A1, 5)
```

### Extract Numeric Value from Text

Text containing number in A1 (like "Order #12345").

```
=SUMPRODUCT(MID(0&A1, LARGE(INDEX(ISNUMBER(--MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1))*ROW(INDIRECT("1:"&LEN(A1))), 0), ROW(INDIRECT("1:"&LEN(A1))))+1, 1)*10^ROW(INDIRECT("1:"&LEN(A1)))/10)
```

Simpler if number position is known:
```
=VALUE(MID(A1, 8, 5))
```

### Concatenate with Delimiter

Join A1:A10 with commas.

```
=TEXTJOIN(", ", TRUE, A1:A10)
```

## Dynamic Array Examples (Excel 365+)

### Filter and Sort Combined

Filter sales over 1000 and sort descending.

```
=SORT(FILTER(A2:C100, B2:B100>1000), 2, -1)
```

### Top N Results

Top 5 sales with all columns.

```
=SORT(A2:C100, 2, -1)
```

Then use:
```
=INDEX(SORT(A2:C100, 2, -1), SEQUENCE(5), {1,2,3})
```

### Unique Values Sorted

Unique regions sorted alphabetically.

```
=SORT(UNIQUE(A2:A100))
```

### Dynamic Dependent Dropdown List

Unique values from column A where column B matches G1.

```
=SORT(UNIQUE(FILTER(A2:A100, B2:B100=G1)))
```

### Generate Date Series

Monthly dates for a year starting from A1.

```
=EDATE(A1, SEQUENCE(12, 1, 0, 1))
```

### Transpose Data

Convert A1:A10 to horizontal.

```
=TRANSPOSE(A1:A10)
```

### Running Total with SCAN

Values in A2:A10.

```
=SCAN(0, A2:A10, LAMBDA(a, b, a+b))
```

### Rank All Values

Rank each value in A2:A100.

```
=MAP(A2:A100, LAMBDA(x, RANK(x, A2:A100)))
```

## Error-Safe Patterns

### Division with Zero Check

```
=IFERROR(A1/B1, 0)
```

Or explicit check:
```
=IF(B1=0, 0, A1/B1)
```

### Lookup with Multiple Fallbacks

Try XLOOKUP, then VLOOKUP backup, then default.

```
=IFERROR(XLOOKUP(E1, A:A, B:B), IFERROR(VLOOKUP(E1, D:E, 2, FALSE), "Not Found"))
```

### Empty Cell Handling

Return blank instead of 0 for empty inputs.

```
=IF(A1="", "", A1*B1)
```

### Percentage with Zero Denominator

```
=IFERROR(A1/B1, 0)
```

Formatted:
```
=TEXT(IFERROR(A1/B1, 0), "0.0%")
```

### Nested IFERROR Chain

Try multiple lookups in sequence.

```
=IFERROR(VLOOKUP(E1, Table1, 2, FALSE),
 IFERROR(VLOOKUP(E1, Table2, 2, FALSE),
 IFERROR(VLOOKUP(E1, Table3, 2, FALSE), "Not Found")))
```

## Data Validation Formulas

### Validate Email Format

Check if A1 contains @ and . after @.

```
=AND(ISNUMBER(FIND("@", A1)), ISNUMBER(FIND(".", A1, FIND("@", A1))))
```

### Check Date is Weekday

Date in A1, TRUE if weekday.

```
=AND(WEEKDAY(A1, 2)<=5)
```

### Value Within Range

A1 must be between 1 and 100.

```
=AND(A1>=1, A1<=100)
```

### No Duplicates Check

Check if A1 is unique in column A.

```
=COUNTIF(A:A, A1)=1
```

### Future Date Only

A1 must be after today.

```
=A1>TODAY()
```

### Text Length Validation

A1 must be exactly 10 characters.

```
=LEN(A1)=10
```

## Financial Calculations

### Compound Interest

Principal in A1, rate in B1 (annual), years in C1.

```
=A1*(1+B1)^C1
```

### Monthly Payment (Loan)

Loan amount in A1, annual rate in B1, years in C1.

```
=PMT(B1/12, C1*12, -A1)
```

### Net Present Value

Rate in A1, cash flows in B1:B10.

```
=NPV(A1, B1:B10)
```

### Internal Rate of Return

Cash flows in A1:A10 (first value is initial investment, negative).

```
=IRR(A1:A10)
```

### Depreciation (Straight Line)

Cost in A1, salvage in B1, life in C1.

```
=SLN(A1, B1, C1)
```

## Array Formula Patterns

### Count Cells Meeting Multiple OR Conditions

Count where A:A is "East" OR "West".

```
=SUMPRODUCT((A2:A100="East")+(A2:A100="West")>0)
```

### Sum Where Any Condition Met

Sum B:B where A:A contains "Apple" OR "Orange".

```
=SUMPRODUCT(((A2:A100="Apple")+(A2:A100="Orange"))*B2:B100)
```

### Return Nth Match

Find 3rd occurrence of "Error" in A:A, return corresponding B value.

```
=INDEX(B:B, SMALL(IF(A2:A100="Error", ROW(A2:A100)), 3))
```

Note: May require Ctrl+Shift+Enter in older Excel versions.

### Conditional Concatenation

Join all values in B:B where A:A matches E1.

```
=TEXTJOIN(", ", TRUE, IF(A2:A100=E1, B2:B100, ""))
```

Excel 365+, or Ctrl+Shift+Enter in older versions.

## Performance Tips

### Use Tables for Auto-Expanding Ranges

Instead of:
```
=SUM(A:A)
```

Use structured references:
```
=SUM(Table1[Sales])
```

### Avoid Volatile Functions in Large Datasets

Volatile functions (TODAY, NOW, RAND, OFFSET, INDIRECT) recalculate constantly.

Replace:
```
=SUMIF(INDIRECT("A:A"), "East", INDIRECT("B:B"))
```

With:
```
=SUMIF(A:A, "East", B:B)
```

### Use SUMIFS Instead of SUMPRODUCT for Simple Conditions

More efficient:
```
=SUMIFS(C:C, A:A, "East", B:B, ">1000")
```

Less efficient:
```
=SUMPRODUCT((A2:A1000="East")*(B2:B1000>1000)*C2:C1000)
```

### Limit Range References

Instead of entire columns:
```
=VLOOKUP(E1, A:B, 2, FALSE)
```

Use defined ranges:
```
=VLOOKUP(E1, A1:B1000, 2, FALSE)
```