Function Problems

How to Create Categories Based on Two Columns in Excel

Maira Mehtab Sep 22, 2026 872 views

Question details

The user needs to assign specific categories to data based on combinations of values found in two separate columns (e.g., Columns B and C).

Product: Excel
Device & OS: not provided
Scenario: Processing a large dataset of approximately 15,000 rows where data categorization depends on matching multiple criteria simultaneously.
Observed behavior: Categorizing data accurately across large rows requires a robust multi-criteria lookup solution instead of manual data entry.

Before you start

Before applying multi-condition formulas to large datasets, format your data and mapping list as structured Tables (Ctrl+T) to improve formula performance and ensure calculations run smoothly.

Solution 1Recommended

Use an INDEX and MATCH Array Formula

Create a mapping table and use a multi-condition array formula to look up and assign categories based on two conditions simultaneously.

This method relies on creating a logical array where two separate conditions must both be TRUE to return the corresponding category.

It is highly effective for any spreadsheet tool supporting standard array functions.

Create a Mapping Table

On a new worksheet, create a reference table containing three columns: Condition 1 (Column G), Condition 2 (Column H), and the corresponding Category (Column I).

Select the Target Cell

In your main dataset, click on the first cell in the row where you want the new category to be populated.

Input the Lookup Formula

Enter the formula: =INDEX(I:I, MATCH(1, (G:G=B2)*(H:H=C2), 0)). Ensure that B2 and C2 reference the two cells in your main dataset that need evaluating.

Apply as an Array Formula

Press Ctrl+Shift+Enter (instead of just Enter) to evaluate it as an array formula. Double-click the fill handle in the bottom-right corner of the cell to drag the formula down across all 15,000 rows.

Performance Tip: Using structured table references (e.g., Table1[ColumnName]) or exact ranges (like G2:G100) instead of full column references (like G:G) will significantly speed up calculation time on large files.

Solution 2

Merge Queries Using Power Query

For very large datasets, Power Query provides a more stable, formula-free way to assign categories based on multiple columns.

Categorize Data Easily

Seamlessly Manage Multi-Condition Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas, complex lookups like INDEX/MATCH, and easily handles large datasets. You can categorize tens of thousands of rows using the same familiar features you expect.

1. Open Your Data: Launch WPS Spreadsheet and open the file containing your 15,000-row dataset.
2. Create Mapping Reference: Set up a mapping reference table on an adjacent sheet containing the required two-column combinations and their respective categories.
3. Insert the Formula: Navigate to the Formula tab, or directly type your =INDEX(..., MATCH(...)) array formula into the target column.
4. Apply and Save: Use the fill handle to apply the logic down the column and save your categorized document perfectly intact as an .xlsx file.

Fully compatible with Microsoft Excel file formats (.xlsx, .xls, .csv).Seamlessly executes complex INDEX and MATCH array formulas with high performance.Lightweight application that processes large datasets of 15,000+ rows without lagging.Includes intuitive built-in data processing and filtering tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDEX MATCH formula return an #N/A error?

The #N/A error occurs when the formula cannot find an exact match for both conditions in your mapping table. Double-check for trailing spaces, mismatched data types (such as text formatted as numbers), or verify that the specific B and C combination actually exists in your reference list.

Can I use XLOOKUP for two columns instead of INDEX MATCH?

Yes. If your spreadsheet software supports XLOOKUP, you can use the formula =XLOOKUP(1, (Condition1Range=B2)*(Condition2Range=C2), CategoryRange). This method is often preferred because it natively handles arrays and does not require pressing Ctrl+Shift+Enter.

What happens if there are duplicate condition combinations in the mapping table?

Standard lookup formulas, including INDEX MATCH and XLOOKUP, evaluate data from top to bottom. If there are duplicates, the formula will return the category associated with the very first matching combination it encounters in the mapping table.

Excel & Data Problems