How to Populate Excel Output from Repeating Input Items with INDEX MATCH in WPS Spreadsheets
Question details
The user needs to transfer band numbers and bandwidth classes from an Input sheet to an Output sheet using formulas, even when each item has a different number of entries.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The Output sheet must automatically show the correct band number and bandwidth class for each repeating item that appears in the Input sheet.
- Observed behavior
- The output currently requires a formula-based lookup so the correct row is returned for items with varying numbers of records.
Open the workbook and identify the exact Input sheet range, the item identifier column, and the columns that store band numbers and bandwidth classes. Also confirm the Output sheet has a matching item identifier or row reference that can be used in the lookup.
Use INDEX and MATCH to populate Output rows from repeating Input items
Use this when each item in the Input sheet can have a different number of rows and the Output sheet needs the matching band number or bandwidth class automatically.
INDEX returns the value at a row and column intersection, and MATCH finds the position of the item identifier in the Input sheet. Combining them lets you return the band information that belongs to the current Output row without hard-coding row numbers.
In WPS Office, open the workbook, click the Output sheet tab, and select the first cell where the band number should appear.
Type =INDEX(Input!$A:$AG,MATCH($A2,Input!$A$2:$A$5,0)+ROW()-2,COLUMN()) and press Enter. Replace Input!$A:$AG with the actual Input sheet range, and replace $A2 with the first item identifier in the Output sheet.
Drag the fill handle from the lower-right corner of the selected cell across the output columns for band number and bandwidth class, then down through all output rows.
Test the formula against several sample items. If an item repeats more than once, change the lookup range or add a unique occurrence column so the correct row is returned for each entry.
Use HLOOKUP or CONCATENATE for horizontal and combined output layouts
Use this when the source bands are arranged horizontally or when you need to join several returned values into one Output cell.
Populate Repeating Input Rows in WPS Spreadsheets with INDEX MATCH
WPS Spreadsheets supports the same INDEX, MATCH, HLOOKUP, and CONCATENATE formulas used in Excel, so you can build a formula-driven Output sheet that automatically pulls band numbers and bandwidth classes from repeating Input rows without manual copying.
- 1. Download and install WPS Office: Go to the WPS Office download page, download the free installer, and install WPS Office on your computer.
- 2. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Spreadsheets, then open the Excel workbook that contains the Input and Output sheets.
- 3. Enter the INDEX MATCH formula: Select the first Output cell, type =INDEX(Input!$A:$AG,MATCH($A2,Input!$A$2:$A$5,0)+ROW()-2,COLUMN()), and press Enter.
- 4. Copy the formula and verify the result: Drag the fill handle across the output columns and down the rows, then compare a few returned values with the Input sheet.
Frequently Asked Questions
Why does the Output sheet show #N/A after using INDEX MATCH?
Usually the item identifier in the Output sheet does not exactly match the Input sheet, or the MATCH lookup range does not include the item column. Check for extra spaces, duplicate identifiers, and make sure the range starts at the correct cell.
Can I return all rows for an item when the number of entries is different?
A single INDEX MATCH returns the first match. To return multiple rows, add a helper column in the Input sheet that assigns a unique occurrence number to each row for an item, or use WPS Spreadsheets filter and paste links for a non-formula approach.
Does this formula work in Microsoft Excel as well?
Yes, INDEX, MATCH, HLOOKUP, and CONCATENATE are standard spreadsheet functions, and the same formulas work in WPS Spreadsheets and Excel .xlsx files.
How do I adjust the formula for a different Input range?
Replace the range and MATCH lookup array with your actual Input sheet range, such as Input!$A$2:$D$100, and keep the column return reference aligned with the value you want.
