Function Problems

How to Use the TOCOL Function with Multiple Conditions in Excel

Camila Milosovich Sep 28, 2026 871 views

Question details

The user needs to extract a list of dates from a range based on multiple conditions, specifically looking for dates marked as "O" (Off) or "BH" (Bank Holiday) within a 28-day timeframe.

How to Use the TOCOL Function with Multiple Conditions in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Extracting and formatting specific date entries into a single column from a large dataset based on specific status codes and date boundaries.
Observed behavior: The goal is to successfully return a single column of dates that meet all criteria without returning errors or blank cells.

Before you start

Ensure you are using a version of Excel that supports dynamic array functions like TOCOL and FILTER (Microsoft 365 or Excel 2021 and later). Verify that your dataset ranges are perfectly aligned in size to prevent formula errors.

Solution 1Recommended

Combine TOCOL and FILTER with Boolean Logic

Use the TOCOL and FILTER functions combined with multiplication and addition to apply "AND" and "OR" logic across your conditions.

In Excel dynamic arrays, multiplication (*) acts as an AND operator, requiring all conditions to be true. Addition (+) acts as an OR operator, allowing either condition to be true. By nesting these inside the FILTER function and wrapping it in TOCOL, you can collapse the filtered row data into a single column.

Select the destination cell

Click on the cell where you want the filtered single column of dates to appear.

Enter the combined formula

Type =TOCOL(FILTER(J2:MH2,(J2:MH2>=F1)*(J2:MH2<=F1+28)*((J3:MH3="O")+(J3:MH3="BH")),"")) into the formula bar.

Adjust cell references

Ensure J2:MH2 represents your date row, F1 is your start date, and J3:MH3 represents your criteria row containing 'O' or 'BH'. Press Enter to execute.

Combine TOCOL and FILTER with Boolean Logic

Formula Breakdown: The * ensures the date is >= F1 AND <= F1+28. The + ensures the status is either O OR BH. TOCOL then transposes the horizontal array into a vertical column.

Use dynamic arrays in WPS Spreadsheet

Easily Manage Array Formulas in WPS Office

WPS Spreadsheet fully supports advanced dynamic array functions like FILTER and TOCOL, allowing you to seamlessly process complex multi-condition data queries.

1. Open WPS Spreadsheet: Launch WPS Office and open your Excel workbook containing the dataset.
2. Select the target cell: Click the cell where you want the extracted list to be displayed.
3. Insert the formula: Enter your =TOCOL(FILTER(...)) formula into the formula bar just as you would in Microsoft Excel and press Enter.

Full compatibility with Microsoft Excel functions and array formulasFree and lightweight alternative to heavy spreadsheet toolsIntuitive formula builder and syntax highlightingCross-platform support for Windows, Mac, and Linux

microsoft office alternative - wps office

Frequently Asked Questions

Why does my TOCOL and FILTER formula return a #CALC! error?

A #CALC! error typically occurs when the FILTER function finds no matches for your specified conditions. To fix this, provide an "if_empty" value at the end of your FILTER formula, such as "" (two double quotes) to return a blank cell instead of an error.

Can I use older versions of Excel to run the TOCOL function?

No, TOCOL is a dynamic array function available only in Microsoft 365, Excel for the Web, and Excel 2021 or newer. If you are on an older version, you will need to use a complex INDEX and AGGREGATE function combination instead.

What does the multiplication (*) and addition (+) mean in Excel array formulas?

In Excel array formulas, the asterisk (*) functions as an "AND" logic operator, meaning all joined conditions must be met. The plus sign (+) acts as an "OR" logic operator, meaning the formula will return true if at least one of the conditions is met.

How do I ignore blanks or errors when using TOCOL?

The TOCOL function has an optional second argument [ignore]. You can set it to 1 to ignore blanks, 2 to ignore errors, or 3 to ignore both blanks and errors. For example: =TOCOL(FILTER(...), 3).

Excel & Data Problems