Function Problems

How to Automatically Populate Excel Categories from a Study Value

WPS Content Manager Sep 27, 2026 869 views

Question details

The user wants to automatically assign or populate a category in a spreadsheet based on an inputted study value, eliminating the need for manual data entry.

How to Automatically Populate Categories Based on Study Values in Excel

Product: Excel
Device & OS: not provided
Scenario: Entering study data and needing the spreadsheet to automatically retrieve and display the associated category or faculty from a reference table.
Observed behavior: Currently, assigning categories requires manual input. The goal is to establish a mapping table and use dynamic lookup formulas to automate the classification.

Before you start

Ensure your source data does not contain trailing spaces and set up a clear, dedicated mapping table with category headers before applying lookup formulas.

Solution 1Recommended

Use the FILTER and BYCOL Formula

This approach uses dynamic array functions to check across multiple columns and return the correct category header.

This method is highly efficient if you have your categories listed as headers, with the respective study values listed in the rows beneath each header.

Create a mapping table

Set up your reference data. For example, place your category headings in cells E1 and F1, and list the corresponding study mappings in the range E2:F5.

Select the target cell

Click on the cell in your main dataset where you want the automated category to appear (e.g., cell B2).

Enter the FILTER formula

Type the formula =FILTER($E$1:$F$1,BYCOL($E$2:$F$5=A2,LAMBDA(a,OR(a)))) where A2 contains the study value you want to look up.

Apply to remaining rows

Press Enter to calculate the result. Click and drag the fill handle at the bottom-right corner of the cell to copy the formula down your column.

Version Compatibility: The BYCOL and LAMBDA functions require modern versions of spreadsheet software that support dynamic arrays.

Solution 2

Use an INDEX and FIND Formula with a Structured Table

Ideal for users who organize their reference data using Excel Tables for easier formula reading and automatic range expansion.

Automate Data Entry and Lookups with WPS Spreadsheet

WPS Spreadsheet offers powerful dynamic array functions and full support for complex mapping formulas, making it easy to automate category assignments without manual work.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook.
2. Set up your reference table: Create your mapping table with categories as column headers.
3. Apply the lookup formula: Input the FILTER or INDEX formula in your target cell to instantly map values across your dataset.

Fully compatible with Microsoft Excel formulas and the .xlsx format.Built-in support for advanced functions like FILTER, INDEX, and array formulas.Free, lightweight, and fast processing for large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my lookup formula return a #CALC! or #N/A error?

This usually happens if there are hidden spaces in your study values or if the lookup value doesn't exactly match any entry in your mapping table. Use the TRIM function to clean your data or check for typos.

Can I use VLOOKUP instead of FILTER or INDEX for this?

VLOOKUP works best when your lookup value is in the first column of a vertical table and you want to return a value to the right. Since this scenario requires searching for a value across multiple columns to return a top header, INDEX/MATCH or FILTER/BYCOL is the correct approach.

How do I update the formula if I add more categories?

If you are using standard ranges (like $E$1:$F$1), you will need to manually expand the range in the formula to include the new columns (e.g., $E$1:$G$1). If you use a Structured Table, the formula updates automatically when you add new columns.

Excel & Data Problems