Function Problems

How to Automatically Assign Serial Numbers Based on Excel Criteria

Muhammad Talha Oct 9, 2026 869 views

Question details

The user wants to automatically generate and assign specific serial numbers based on the values present in another adjacent column.

How to Automatically Assign Serial Numbers Based on Criteria in Excel

Product: Excel
Device & OS: not provided
Scenario: Generating dynamic serial numbers depending on the criteria of adjacent cells to streamline and automate data entry workflows.
Observed behavior: Serial numbers need to be accurately mapped from a predefined set of rules or a lookup table and must update automatically whenever the criteria column values change.

Before you start

Ensure your data is organized with clear criteria values in a dedicated column, and determine the exact serial number mapping rules you wish to apply before writing the formulas.

Solution 1Recommended

Use XLOOKUP with a Mapping Table for Scalability

Recommended for assigning serial numbers when you have multiple criteria or anticipate adding more rules in the future, as it makes maintaining data much easier.

A lookup table separates your logic from your formula, making it simple to update. XLOOKUP is highly efficient for referencing these mapping tables and allows you to set a default value if the criteria is not met.

Create a mapping table

In an empty space on your worksheet (e.g., columns P and Q), create a two-column mapping table. Enter your criteria in the first column (e.g., P2:P4) and the corresponding serial numbers in the second column (e.g., Q2:Q4).

Enter the XLOOKUP formula

Select the first cell in your target serial number column (e.g., N2) and enter the formula: =XLOOKUP(B2,$P$2:$P$4,$Q$2:$Q$4,"TKN-004").

Apply to the entire column

Press Enter to calculate the first result, then double-click or drag the fill handle in the bottom-right corner of the cell to copy the formula down your target column.

Use XLOOKUP with a Mapping Table for Scalability

Fallback Value in XLOOKUP: The "TKN-004" at the end of the XLOOKUP formula acts as a default fallback value. If the criteria in cell B2 is not found in your mapping table, Excel will automatically assign this default serial number.

Solution 2

Use Nested IF Functions for Simple Criteria

Best for scenarios with a very small number of fixed criteria conditions, allowing you to assign a specific serial number directly inside the formula without extra tables.

Efficient Data Management with WPS Spreadsheet

Automatically Assign Serial Numbers in WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical and lookup functions like IF and XLOOKUP, allowing you to easily map and assign serial numbers without any compatibility issues or steep learning curves.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing your data.
2. Set up your mapping table: Identify the criteria column and set up a small mapping table in a blank area of your sheet.
3. Enter the formula: Type your preferred formula (=XLOOKUP or =IF) into the target cell to establish the automated logic.
4. Apply to the dataset: Drag the fill handle to automatically assign the mapped serial numbers to the entire column.

Fully compatible with Microsoft Excel formulas, formatting, and file types.Seamless handling of XLOOKUP, VLOOKUP, and complex nested IF statements.Free and lightweight spreadsheet tool for professional data analysis.

Frequently Asked Questions

Can I use VLOOKUP instead of XLOOKUP to assign serial numbers?

Yes, you can use VLOOKUP if XLOOKUP is not available in your version. Ensure your mapping table's criteria are in the leftmost column, and use a formula like =VLOOKUP(B2, $P$2:$Q$4, 2, FALSE) to retrieve the correct serial numbers.

What if my criteria are text values instead of numbers?

Both IF and XLOOKUP work perfectly with text values. Simply enclose your text criteria in double quotes within the formula. For example: =IF(B2="Pending","TKN-001","TKN-002").

How do I handle empty cells in the criteria column so they don't assign a default serial number?

You can wrap your main formula inside an initial IF statement to check for blanks. Use =IF(ISBLANK(B2), "", XLOOKUP(B2,$P$2:$P$4,$Q$2:$Q$4,"TKN-004")) to leave the serial number cell completely blank when no criteria is provided.

Excel & Data Problems