Function Problems

How to Return Matching Data From Two Excel Columns Using FILTER

Phi Hung Vo Sep 28, 2026 871 views

Question details

The user needs to extract specific matching data, such as member call dates and call reasons, from multiple columns in a large data log using dynamic array formulas.

How to Return Matching Data From Two Excel Columns Using FILTER

Product: Excel
Device & OS: not provided
Scenario: Filtering a large call log dataset to retrieve specific columns of data based on a matching member ID.
Observed behavior: The user wants to display targeted results from multiple columns based on specific criteria without manually hiding or copying irrelevant columns.

Before you start

Ensure you are using a modern spreadsheet version that supports Dynamic Array formulas, such as FILTER and CHOOSECOLS, and verify that there is enough empty space adjacent to your formula cell to accommodate the spilled results.

Solution 1Recommended

Return Selected Columns Using CHOOSECOLS and FILTER

Combine the CHOOSECOLS and FILTER functions to extract matching data from specific, non-adjacent columns simultaneously.

This method is highly effective when you have a large table but only want to return specific columns (like the 3rd and 6th columns) for rows that meet your criteria.

Select the target cell

Click on the empty cell where you want the top-left corner of your extracted data to begin.

Enter the combined formula

Type the formula =CHOOSECOLS(FILTER(A8:F14,C8:C14="PQR678"),3,6) into the formula bar. This checks column C for the ID 'PQR678' and extracts only columns 3 and 6 from the matching rows.

Apply and review results

Press Enter. The data will automatically spill into the adjacent cells. Ensure there is enough space below and to the right to avoid a #SPILL! error.

Date Formatting: The extracted dates will display according to your system's regional settings. You can quickly reformat them by selecting the spilled date column and using the Number Format dropdown on the Home tab.

Solution 2

Extract Data from a Single Column Using FILTER

Use the standard FILTER function to return values from one specific column when a single condition is met.

Solution 3

Manage Large Datasets Using LET, CHOOSECOLS, and FILTER

Use the LET function to assign names to calculation results, preventing blank cells from displaying as zeros and streamlining complex formulas for large ranges.

Powerful Data Filtering

Filter and Extract Data Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like FILTER, CHOOSECOLS, and LET. You can seamlessly manage large datasets, extract matching records, and analyze your data with high performance without switching software.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your data log.
2. Select the destination cell: Click on the cell where you want the filtered data to appear.
3. Enter the array formula: Type your =FILTER() or =CHOOSECOLS() formula directly into the formula bar.
4. View instant results: Press Enter to instantly spill the matching results into the adjacent rows and columns.

100% compatible with Microsoft Excel formulas and array functionsBuilt-in support for modern functions including FILTER and CHOOSECOLSLightweight software that processes large data logs smoothlyCompletely free to use for daily professional and personal tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a #SPILL! error when using the FILTER function?

A #SPILL! error occurs when there is not enough empty space for the formula to display all the returned data. To fix it, ensure that all cells below and to the right of your formula cell are completely blank so the dynamic array can expand properly.

How do I prevent the FILTER function from showing a #CALC! error when there is no match?

You can prevent the #CALC! error by utilizing the third optional argument in the FILTER function, which is [if_empty]. Simply add a comma and empty quotation marks at the end of your formula, like this: =FILTER(F8:F13, C8:C13=C14, "").

Can I filter data based on multiple criteria?

Yes, you can use the multiplication operator (*) for AND logic or the addition operator (+) for OR logic within the FILTER criteria. For example, =FILTER(A8:F14, (C8:C14="PQR678") * (B8:B14="Active"), "") filters rows that meet both conditions.

Why are the extracted dates showing up as random numbers?

Spreadsheets store dates as sequential serial numbers. If your extracted date appears as a number (like 44123), simply select the cells, right-click to choose 'Format Cells', and apply a 'Date' format from the Number tab.

Excel & Data Problems