Function Problems

How to Place Values in Different Excel Columns Based on a Code

Rana GarciaRana Garcia Oct 15, 2026 872 views

Question details

The user needs to distribute specific numerical values into different adjacent columns automatically, based on a distinct text code found in a reference cell.

How to Place Values in Different Excel Columns Based on a Code
Product
Excel
Device & OS
not provided
Scenario
Organizing and categorizing data where a value must be dynamically routed to a specific destination column (e.g., columns M, N, O, P, Q) depending on an assigned category code (e.g., SE, MW, SC, W, NE).
Observed behavior
The goal is to display the value in the correct column corresponding to its code while leaving the other columns blank for that specific row.
Before you start

Ensure your dataset is organized in clear columns, identifying exactly which column contains your condition codes and which column holds the values to be moved.

Solution 1Recommended

Use Standard IF Formulas with Mixed References

Using the IF function with locked column references is the most reliable and backward-compatible method to distribute values horizontally.

By utilizing absolute column references (the dollar sign $), you can drag the formula across your sheet without breaking the connection to your main condition column.

1
Select the destination cell

Click on the first cell of your first destination column (for example, M5) where the condition's value should appear.

2
Enter the IF statement

Type the formula =IF($F5="SE",$G5,""). In this formula, $F5 is the cell with the code, "SE" is the specific code for column M, and $G5 contains the value you want to place.

3
Configure the remaining columns

Select the adjacent cells (N5, O5, P5, Q5) and input the same formula structure, updating the code condition for each column. For example, in N5, enter =IF($F5="MW",$G5,"").

4
Apply across your dataset

Highlight the cells from M5 through Q5, then click and drag the fill handle (the small square at the bottom right) down to copy these formulas across all required rows.

Use Standard IF Formulas with Mixed References
Blank Cells Maintained: The empty quotation marks ("") at the end of the formula ensure that if the code does not match, the cell will remain visually empty instead of displaying FALSE.
Advanced Data Management

Easily Distribute Data Using IF Functions in WPS Spreadsheet

WPS Spreadsheet fully supports standard and complex IF functions, allowing you to categorize and place data automatically with full compatibility with Microsoft Excel formulas.

  1. 1. Open your data file: Launch WPS Spreadsheet and open your existing dataset.
  2. 2. Select the target cell: Click on the cell where the value should be placed.
  3. 3. Input the formula: Type your IF formula, referencing the code column and the value column with standard syntax.
  4. 4. Drag to apply: Press Enter and drag the fill handle to apply the formula across your entire table.
Fully compatible with Microsoft Excel formula syntax (.xlsx)Lightweight and runs smoothly on Windows, Mac, and mobileIntuitive interface for building complex conditional formulas
microsoft office alternative - wps office

Frequently Asked Questions

Why is my IF formula returning FALSE instead of leaving the cell blank?

This happens if you omit the third argument in the IF function. Make sure to include commas and empty quotation marks at the end of your formula, like this: =IF($F5="SE",$G5,"").

Can I use a VBA macro to place values based on a code?

Yes, you can use a Worksheet_Change macro to monitor the code cell and automatically place the value into the destination cell. However, standard IF formulas are generally recommended as they are simpler to implement, faster, and easier to maintain for most users.

Why do I need to use dollar signs ($) in the formula?

The dollar signs create mixed references. Writing $F5 locks the column reference to F so that when you drag and copy the formula to the right (into columns N, O, etc.), Excel still accurately checks column F for your condition code.