How to Place Values in Different Excel Columns Based on a Code
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.

- 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.
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.
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.
Click on the first cell of your first destination column (for example, M5) where the condition's value should appear.
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.
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,"").
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 Dynamic Array Formulas (Microsoft 365 Only)
If you are using Microsoft 365, a dynamic array formula allows you to spill the results automatically without needing to copy the formula across multiple columns manually.
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. Open your data file: Launch WPS Spreadsheet and open your existing dataset.
- 2. Select the target cell: Click on the cell where the value should be placed.
- 3. Input the formula: Type your IF formula, referencing the code column and the value column with standard syntax.
- 4. Drag to apply: Press Enter and drag the fill handle to apply the formula across your entire table.

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.




