How to Fix Excel #VALUE! Error in Partial-Match FILTER Formulas
Question details
The user needs to return specific column headers when a search term appears anywhere within a multi-column data range, but using a partial match in the FILTER formula is returning a #VALUE! error.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Searching for a partial text string (e.g., in cell A9) across a large 2D data range (C2:G100) to extract the corresponding column headers (C1:G1).
- Observed behavior
- Attempting to use SEARCH inside a standard FILTER function for a 2D array causes a dimension mismatch, resulting in a #VALUE! error because FILTER requires a 1D array for its include condition.
Ensure your version of Excel or your spreadsheet software supports dynamic array functions such as FILTER, TOCOL, BYCOL, and LAMBDA, which are essential for processing complex 2D arrays.
Use TOCOL and BYCOL to Fix the Dimension Mismatch
This solution reduces the 2D search array into a 1D array using BYCOL, allowing the FILTER function to evaluate the columns correctly without throwing a #VALUE! error.
The #VALUE! error occurs because the FILTER function expects a one-dimensional array to match the single row of headers (C1:G1), but SEARCH returns a two-dimensional array from the data range (C2:G100).
By wrapping the search logic in BYCOL, we compress the 2D results into a 1D array of TRUE/FALSE values, which FILTER can then process perfectly.
Click on cell A13 (or your preferred empty cell) where you want the matching column headers to appear.
Type the following formula: =TOCOL(FILTER(C1:G1,BYCOL(ISNUMBER(SEARCH(A9,C2:G100))*COLUMN(C2:G100),LAMBDA(r,SUM(r)))>0,""))
Press Enter. The formula will automatically spill the matching column headers vertically into the cells below.
Return Comma-Separated Headers using TEXTJOIN
If you prefer the matching headers to be combined into a single cell rather than spilling across multiple cells, you can wrap the FILTER logic inside a TEXTJOIN function.
Identify Exact Rows and Columns for Ambiguous Matches
Partial matching can sometimes return false positives (e.g., searching 'anfield' matching 'Canfield'). Use the LET function to retrieve exact cell coordinates to verify the correct record.
Easily Handle Dynamic Array Formulas in WPS Office
WPS Spreadsheet fully supports modern dynamic array functions like FILTER, TOCOL, BYCOL, and LET. You can flawlessly perform complex partial-match searches across massive datasets without worrying about dimension mismatch errors.
- 1. Open Your Data File: Launch WPS Spreadsheet and open the workbook containing your headers and data range.
- 2. Select Your Output Cell: Click on the cell where you want the extracted column headers to begin spilling.
- 3. Enter the Array Formula: Paste the provided TOCOL and FILTER formula into the formula bar and press Enter to instantly generate the results.
Frequently Asked Questions
Why does using SEARCH inside FILTER cause a #VALUE! error?
The FILTER function requires a one-dimensional boolean array for its include argument (to match the 1D header row). When you use SEARCH on a 2D range (like C2:G100), it returns a 2D array. Because the dimensions do not match, FILTER cannot process it, resulting in a #VALUE! error.
How does the BYCOL function fix the dimension mismatch?
BYCOL processes the 2D array column by column. By applying a LAMBDA function that evaluates each column, it reduces the 2D matrix into a 1-dimensional array of TRUE/FALSE values. This 1D array perfectly matches the header range requirement for the FILTER function.
What should I do if my partial match formula returns the wrong columns?
Because partial matches are broad (e.g., searching 'anfield' might accidentally match 'Canfield'), you can receive unexpected headers. To fix this, make your search term more specific, use exact-match conditions if applicable, or use a LET formula to extract the exact row numbers alongside the headers for manual verification.
Are these dynamic array functions available in older versions of Excel?
No, functions like FILTER, TOCOL, BYCOL, and LAMBDA are part of the modern dynamic array engine introduced in Microsoft 365 and Excel 2021. If you are using Excel 2019 or older, these formulas will return a #NAME? error. Using a modern alternative like WPS Office ensures full support for these functions.
