Function Problems

Excel Formula to Compare Columns B and C and Return Values from Column A

Partner Editor Sep 27, 2026 869 views

Question details

The user needs an Excel formula to compare data in columns B and C, identify values greater than 21, extract the corresponding values from column A, and calculate the differences. The solution must support dynamic data expansion up to 5000 rows.

Excel Formula to Compare Columns B and C and Extract Column A Values

Product: Microsoft Excel
Device & OS: not provided
Scenario: Comparing two columns to dynamically extract related records from a third column and calculate their differences, generating specific Cx and Cy output sequences.
Observed behavior: The user requires an automated method using functions that expand automatically as rows are added, bypassing manual updates for expanding data sequences.

Before you start

Ensure you are using Excel 2021 or Microsoft 365, as this solution relies on dynamic array functions like FILTER and SEQUENCE which are not available in older versions.

Solution 1Recommended

Use an Excel Table with the FILTER Function

Converting your data to an Excel Table ensures formulas automatically expand as new rows are added, while the FILTER function easily extracts matching records.

By structuring your data as a Table, you avoid hardcoding ranges like A3:C40. Structured references dynamically update if your data expands to 5000 rows, ensuring accurate sequence calculations.

Format Data as Table

Select your data range (A3:C40) and press Ctrl+T. Check 'My table has headers' and click OK to convert it into an Excel Table.

Apply the FILTER Function

Click on the cell where you want the extracted Column A values to appear. Enter a formula like: =FILTER(Table1[Column A], (Table1[Column B]>21)*(Table1[Column C]>21), "No match").

Calculate Differences

In an adjacent cell, use the FILTER function to extract the calculations simultaneously: =FILTER(Table1[Column B]-Table1[Column C], (Table1[Column B]>21)*(Table1[Column C]>21)).

Use an Excel Table with the FILTER Function

Dynamic Spill Range: The results will automatically spill down the column for every matching row. You do not need to drag the formula down manually.

Solution 2

Implement a User-Defined Function (UDF) via VBA

If the built-in functions cannot handle the specific Cx and Cy sequence formatting logic you require, a VBA macro can return a dynamic array tailored to exact rules.

Advanced Spreadsheet Solution

Compare Columns and Filter Data Seamlessly with WPS Spreadsheet

WPS Spreadsheet offers robust support for dynamic array functions, structured table references, and advanced data analysis, making it incredibly easy to extract and compute expanding data ranges.

1. Open your dataset: Launch WPS Spreadsheet and open your document containing the A3:C40 data.
2. Format as Table: Select the data range, go to the Insert tab, and click 'Table' to enable automatic expansion.
3. Use Dynamic Formulas: Type the =FILTER() function in your destination cell to extract values from Column A based on your conditions.
4. Generate Results: Press Enter to generate the dynamic spilled array results instantly.

Fully compatible with Microsoft Excel .xlsx formats and structured referencesBuilt-in support for advanced dynamic array functions like FILTER, SORT, and SEQUENCELightweight architecture handles large datasets up to thousands of rows without lagFree to use with an intuitive, familiar tabbed interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is my FILTER formula returning a #CALC! error?

The #CALC! error typically occurs when the FILTER function finds no matching records (e.g., no rows where values are >21). You can fix this by adding an 'if_empty' argument, such as =FILTER(A:A, B:B>21, "No match").

Can I use dynamic array formulas in older versions of Excel?

Dynamic array functions like FILTER and SEQUENCE are only available in Excel 2021, Microsoft 365, and modern suites like WPS Office. For Excel 2019 or older, you must use complex INDEX/MATCH array formulas entered with Ctrl+Shift+Enter.

How do I calculate differences between two columns inside a dynamic array?

You can perform arithmetic directly on arrays within the formula. For example, =FILTER(Table1[Column B] - Table1[Column C], Table1[Column B] > 21) will return the calculated differences for only those rows meeting the condition.

What does the #SPILL! error mean when extracting Column A?

A #SPILL! error indicates that the dynamic array function requires empty cells to output its expanding results (e.g., if the data expands to row 5000), but existing data is blocking the path. Clear the cells below the formula to resolve this.

Excel & Data Problems