'%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 Matching Values in Excel: INDEX MATCH Alternatives
Question details
The user needs to extract all data records that match a specific lookup value across dynamically selected worksheets.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Pulling multiple related data rows based on a single search criterion, where the target worksheet name is stored in a cell and changes dynamically.
- Observed behavior
- The standard INDEX and MATCH formula stops executing after finding the first match, failing to retrieve any subsequent matching records for the same lookup value.
Verify your current Excel version. The highly recommended FILTER function is only available in Microsoft 365, Excel 2021, and Excel for the web. If you are using an older version (like Excel 2019 or earlier), you will need to use the array formula method.
Use the FILTER Function (Recommended for Modern Excel)
This is the most efficient and robust method to extract multiple matches dynamically without relying on complex array formulas.
The FILTER function is designed specifically to output arrays of data based on criteria. By combining FILTER with the INDIRECT function, you can dynamically build the sheet reference from a cell value and instantly spill all matching records.
Click on the topmost blank cell where you want your multiple matching results to begin displaying.
Type the formula: =FILTER(INDIRECT("'"&J$14&"'!B1:B1000"),INDIRECT("'"&J$14&"'!A1:A1000")=D$12,""). This formula uses INDIRECT to read the worksheet name from cell J14, checks column A for the lookup value in D12, and returns the corresponding data from column B.
Hit Enter on your keyboard. Excel will automatically 'spill' all corresponding matching values down the column into the required number of rows.
Use INDEX, SMALL, and IF (For Older Excel Versions)
Use this classic array formula approach if you are using Excel 2019, 2016, or older versions that do not support dynamic array functions.
Easily Extract Multiple Matches with WPS Office
WPS Spreadsheet natively supports both legacy array formulas and modern dynamic array functions like FILTER. You can effortlessly query multiple matching values and streamline your data analysis for free.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the dataset and dynamic references.
- 2. Navigate to the results area: Click on the specific empty cell where you want to output your list of matching items.
- 3. Apply the dynamic FILTER function: Enter the =FILTER() function, specifying your return array and your logical test criteria. You can combine it with INDIRECT if your sheet references are dynamic.
- 4. Generate your matching list: Press Enter to instantly retrieve and display all matching results from your dataset without manual copying.
Frequently Asked Questions
Why does INDEX and MATCH only return the first value?
By design, standard lookup functions in Excel such as VLOOKUP, XLOOKUP, and MATCH scan data linearly from top to bottom. They stop calculating and return a result as soon as they encounter the very first exact match that meets the specified criteria.
What does a #SPILL! error mean when using the FILTER function?
A #SPILL! error occurs when the FILTER formula attempts to output multiple matching values, but the space below the formula cell is blocked by existing data or text. Clearing the blocking cells will immediately resolve the error and display all matches.
Can I return multiple matches from another workbook?
Yes, you can use the FILTER function across different workbooks. However, both workbooks must be open at the same time for the FILTER function to work correctly. If the source workbook is closed, the formula will return a #REF! error.
