Function Problems

Excel Formula to Move Amounts to Columns Based on a Line Number

Camila Milosovich Oct 19, 2026 869 views

Question details

The user needs an Excel formula to automatically extract an amount from one of two source columns and place it into a specific destination column by matching the row's line number with the destination column's header.

How to Move Amounts to Specific Columns Based on Line Numbers in Excel

Product: Excel
Device & OS: not provided
Scenario: Sorting or allocating expense amounts from a general data list into categorized columns for an accounting or expense chart.
Observed behavior: The amounts must dynamically populate across columns E through I based on whether the line number in column D matches the specific column header in row 4, pulling the correct numerical amount from either column B or C.

Before you start

Ensure that your destination column headers exactly match the line numbers or category identifiers used in your source data rows, and confirm that your data contains no hidden trailing spaces.

Solution 1Recommended

Use a Nested IF Formula with Mixed Cell References

Implement an IF statement using relative and absolute references to dynamically evaluate the line number and distribute the correct amount into the matching column.

This method uses an IF formula to check if the line number for the current row matches the specific column header. If it does, a second embedded IF formula checks which source column contains the amount and returns that value. If the header does not match, the cell is left blank.

To make this work across an entire table, you must lock the column reference for your data (e.g., $D5) and lock the row reference for your headers (e.g., E$4).

Select the first destination cell

Click on the first empty cell in your destination range under the first column header (e.g., cell E5).

Enter the nested IF formula

Type the formula: =IF($D5=E$4,IF($B5<>"",$B5,$C5),""). In this formula, $D5 is your line number, E$4 is the destination header, and $B5/$C5 are your source amounts.

Apply the formula

Press Enter to execute the formula. If the line number matches the header, the amount from column B (or C, if B is empty) will display.

Fill across and down

Click the small square at the bottom-right corner of cell E5, drag it horizontally across your destination columns (e.g., to column I), and then drag it downwards to fill the remaining rows in your chart.

Use a Nested IF Formula with Mixed Cell References

Reference Locking is Essential: Using the dollar sign ($) correctly guarantees the formula stays accurate when dragged. The column must stay fixed for source data ($D5, $B5, $C5) and the row must stay fixed for the headers (E$4).

Efficient Data Management with WPS Spreadsheet

Use WPS Spreadsheet to Manage Expense Allocations

WPS Spreadsheet offers powerful formula calculation capabilities, allowing you to seamlessly process complex nested IF statements and perfectly allocate your financial data into sorted columns.

1. Open your worksheet: Launch WPS Spreadsheet and open the document containing your data list and destination headers.
2. Input the allocation formula: Select the first target cell and enter your nested IF formula. You can use the F4 key on your keyboard to quickly toggle between absolute and relative cell references.
3. Drag to populate: Hover over the bottom-right corner of the cell until the cross cursor appears, then drag across and down to instantly populate your entire expense chart.

Fully compatible with Microsoft Excel formulas, functions, and formats (.xlsx)Lightweight software that processes large data arrays without laggingFree to use with an intuitive, user-friendly interface that requires no learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula return a blank or FALSE instead of the amount?

This usually happens if the cell references are incorrect or not locked properly. Check that your row references for the headers (e.g., E$4) and column references for the line numbers (e.g., $D5) are set correctly with the appropriate $ signs.

Can I use this formula if I have more than two source columns for amounts?

Yes, but you will need to nest additional IF statements or use the IFS function inside the formula to evaluate multiple columns and locate the non-empty amount.

Does this formula work if my headers are text names instead of numbers?

Yes, the exact match logic works for text strings just as well as numbers. Ensure that the text in your line number/category column exactly matches the text in your column headers, without any accidental trailing spaces.

Excel & Data Problems