'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Return Multiple Values by Criteria Using Excel Formulas
Question details
The user needs an Excel formula to extract and return multiple values or rows from a dataset based on specific logical criteria.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Extracting subsets of data that match specific conditions from a larger table or range without relying on manual filtering.
- Observed behavior
- The user wants to output an array of multiple matching results instead of a single lookup value when a condition is met.
Ensure your dataset is organized in clear columns without merged cells, and verify that your version of Excel or WPS Spreadsheet supports dynamic array functions.
Use the FILTER Function for Single or Multiple Criteria
The FILTER function is the most efficient and modern way to extract multiple matching values dynamically.
The FILTER function can return an array of values that meet one or more conditions. By using the multiplication operator (*), you can combine multiple logical criteria to act as an AND condition, meaning all criteria must be true for the row to be returned.
Click on the top-left cell of the blank area where you want the extracted data to appear.
Type the formula structure: =FILTER(array, include, [if_empty]). For example, enter =FILTER(A2:A100, (B2:B100="Criteria1")*(C2:C100="Criteria2"), "No results").
Press Enter. The formula will automatically calculate and spill the matching results into the adjacent cells, displaying 'No results' if no matching data is found.
Use INDEX and AGGREGATE for Older Spreadsheet Versions
If your software does not support dynamic arrays like the FILTER function, you can use a combination of INDEX and AGGREGATE functions.
Filter and Extract Data Dynamically in WPS Spreadsheet
WPS Spreadsheet fully supports dynamic array functions like FILTER, allowing you to instantly extract and analyze multiple values based on complex criteria without relying on clunky legacy formulas.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your source data.
- 2. Select the target cell: Click on the cell where you want to output the extracted values.
- 3. Apply the FILTER function: Type =FILTER(A2:A10, B2:B10="Your Criteria", "Not Found") and press Enter to instantly spill your results.
Frequently Asked Questions
Why is my FILTER formula returning a #CALC! error?
The #CALC! error typically occurs when the FILTER function finds no results matching your criteria, and you haven't provided a value for the optional [if_empty] argument. Add a text string like "No results" as the third argument in your formula to fix this issue.
How do I use OR logic with multiple criteria in the FILTER function?
To return values if *any* of the criteria are met (OR logic), use the plus sign (+) instead of the asterisk (*) between the logical expressions. For example: =FILTER(A2:A100, (B2:B100="Criteria1") + (C2:C100="Criteria2"), "No results").
Can I extract data from multiple columns at once using the FILTER function?
Yes. In the first argument of the FILTER function (the array), simply select all the columns you want to return, such as A2:D100. As long as your criteria ranges match the same number of rows, it will return the entire matching rows across those selected columns.
Why am I getting a #SPILL! error when using the FILTER formula?
A #SPILL! error indicates that the formula is trying to return multiple values, but there is existing data, text, or merged cells blocking the path where the results need to expand. Clear the cells below and to the right of your formula to allow the results to spill properly.
