# Write an Excel formula to categorize records automatically

Builds the formula and lookup table that auto-assigns categories, bands, or tiers to each row based on your rules.

- Topic: Data & Analysis
- Framework: Few-Shot
- Level: Standard
- Tags: Data & Analytics, Write, Fill-in Template
- Source: https://www.thinkwithprompt.com/prompts/write-an-excel-formula-to-categorize-records-automatically

## Prompt

```text
Write an Excel formula that automatically assigns a category to each row in my data.

**What I want to categorize:** [e.g. "customers by revenue tier", "expenses by type based on the description", "tickets by priority", "invoices by days overdue"]
**Columns available:** [PASTE headers and 10-20 sample rows]
**My rules:** [e.g. "over $50k = Tier 1, $10-50k = Tier 2, under $10k = Tier 3" or "if description contains 'Uber' or 'taxi' = Travel"]
**My tool:** [Excel 365 / Excel 2016-2019 / Google Sheets]

Do this:
1. Restate my rules as a clear decision table and point out gaps or overlaps (values that fit two categories or none, boundary values like exactly $10k).
2. Choose the best approach and say why:
   - Number bands: a small lookup table with XLOOKUP approximate match (or VLOOKUP TRUE), not long nested IFs.
   - Text keywords: a keyword table with SEARCH and ISNUMBER, or a FILTER/XLOOKUP pattern.
   - Multiple conditions: IFS with AND/OR, ordered from most to least specific.
3. Keep thresholds and keywords in a separate table, not hard-coded, so I can change them without editing formulas.

Return:
1. The decision table.
2. The lookup table layout to create (sheet name, columns).
3. The exact formula for the first data row, ready to fill down.
4. A test: 5 sample rows with the expected category, including edge cases.
5. How to catch rows that don't match any rule (e.g. return "Review").
6. A Google Sheets version if it differs.
```

## What to fill in

- PASTE headers and 10-20 sample rows
- Excel 365 / Excel 2016-2019 / Google Sheets

Free to copy and use. From ThinkWithPrompt (https://www.thinkwithprompt.com/), a library of 2,663 curated AI prompts.