'%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 Combine Matching Rows from Another Table in Excel 2016
Question details
The user needs to find all rows in a table that contain a specific value from another table and concatenate those matching results into a single new column.
- Product
- Microsoft Excel 2016
- Device & OS
- not provided
- Scenario
- Consolidating and concatenating text data across two different tables based on matching criteria.
- Observed behavior
- The user is unable to use the modern FILTER function to extract and combine matches because Excel 2016 does not support it.
Ensure both of your datasets are formatted as Excel Tables (select your data and press Ctrl+T). This makes referencing columns easier and keeps your formulas or queries dynamic as data grows.
Use Power Query to Merge and Group Data
Power Query is built into Excel 2016 and provides a robust, formula-free way to merge tables and concatenate matching rows without needing the FILTER function.
Power Query can easily handle relational data tasks. By merging your two tables and grouping the results, you can concatenate matches into a single text string using the Text.Combine function.
Select a cell in your first table, go to the Data tab, and click 'From Table/Range'. Once the Power Query Editor opens, close it while choosing 'Keep Connection Only'. Repeat this for the second table.
In Excel, go to Data > Get Data > Combine Queries > Merge. Select your first table from the top dropdown and your second table from the bottom dropdown. Highlight the matching columns in both previews and click OK.
In the Power Query Editor, right-click the matching column and select 'Group By'. Choose the 'All Rows' operation. Then, add a Custom Column using the formula: Text.Combine([GroupedColumnName][TargetColumnToExtract], ", ").
Remove any unnecessary columns, click 'Close & Load' on the Home tab, and place the new consolidated table into your desired worksheet.
Create a Custom VBA Function (UDF)
Write a User-Defined Function in VBA to loop through the second table and concatenate matching values, bypassing version limitations.
Use an Array Formula (If TEXTJOIN is available)
If you are using an Office 365 subscription applied to Excel 2016, you can use an IF array formula combined with TEXTJOIN.
Use WPS Spreadsheet to Seamlessly Filter and Combine Data
WPS Office Spreadsheet supports modern dynamic array functions like FILTER and TEXTJOIN, allowing you to easily concatenate matching rows without needing complex Power Query setups or VBA coding.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your existing Excel file containing the two tables.
- 2. Select the result cell: Click on the cell in the first table where you want the combined matching data to be displayed.
- 3. Apply the modern formula: Type =TEXTJOIN(", ",TRUE,FILTER(Table2[LocNum],ISNUMBER(SEARCH([@Name],Table2[Path])),"")) and press Enter.
- 4. Drag to fill: Click the fill handle at the bottom-right corner of the cell and drag it down to apply the combination logic to the rest of the rows.
Frequently Asked Questions
Why doesn't the FILTER function work in Excel 2016?
The FILTER function is a modern dynamic array function introduced in newer versions of Excel, such as Microsoft 365 and Office 2021. It is not backward-compatible with the standalone (perpetual) release of Excel 2016.
Can I use TEXTJOIN in Excel 2016?
TEXTJOIN is only available in Excel 2016 if you have an active Microsoft 365 subscription. Users with standalone, non-subscription versions of Excel 2016 do not have access to this function and must rely on VBA or Power Query.
Do I need to enable macros to use the VBA solution?
Yes. If you choose to write a custom VBA function to combine your rows, you must ensure macros are enabled in the Trust Center settings and save your file as a Macro-Enabled Workbook (.xlsm).
Is Power Query available in all versions of Excel 2016?
Yes, Power Query is built natively into Excel 2016 under the 'Data' tab (labeled as 'Get & Transform'). It is the most reliable native method for merging and concatenating rows in this version.
