Use One Excel Formula to Skip Rows and Repeat Group Values
Question details
Create one maintainable formula that returns a lookup value for the first occurrence of a group code and leaves later occurrences blank.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains repeated three-character crew or group codes in a destination list and a separate source table containing codes and values.
- Observed behavior
- The required result is to show the matched value only in selected nonconsecutive rows represented by each group code's first occurrence, without manually copying separate formulas or changing references.
Confirm that the destination codes begin with the same three characters used by the source codes. Adjust the A2:A29, G2:G8, and H2:H8 references to match your actual destination and source ranges before filling the formula.
Use an IF, SEARCH, INDEX, and MATCH formula for first occurrences
Use this method when repeated destination codes should remain blank after the first matching row, while the first row for each code returns its source value.
The formula checks whether the current three-character code has appeared earlier in column A. If it is the first occurrence, INDEX and MATCH return the corresponding value from the source table; otherwise, the formula returns a blank cell.
Click the first cell in the output range, such as B2, where you want the matched value to appear.
Type or paste =IF(COUNT(SEARCH(LEFT($A2,3),$A$2:$A2))=1,INDEX(H$2:H$8,MATCH(LEFT($A2,3),LEFT($G$2:$G$8,3),0)),"") into the selected cell.
Press Enter in Microsoft 365, Excel for the web, or Excel 2021. In older Excel versions, press Ctrl+Shift+Enter to confirm it as an array formula.
Drag the fill handle from the formula cell across and down the required output range, such as B2:C29. Keep the dollar signs in the formula so the lookup ranges remain fixed.
Return first-occurrence group values with WPS Spreadsheets
WPS Spreadsheets lets you build and fill a single lookup formula that identifies first-occurrence group codes, returns the matching source value, and leaves repeated rows blank. Its familiar spreadsheet interface makes it easy to edit ranges and apply the formula across your table.
- 1. Open the worksheet in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the Excel workbook containing the destination list and source table.
- 2. Choose the first result cell: Click the first output cell, such as B2, next to the destination code in column A.
- 3. Insert the first-occurrence formula: Enter =IF(COUNT(SEARCH(LEFT($A2,3),$A$2:$A2))=1,INDEX(H$2:H$8,MATCH(LEFT($A2,3),LEFT($G$2:$G$8,3),0)),"") and confirm the entry.
- 4. Fill the formula through the output area: Select the formula cell and drag its fill handle across and down the required result range.
Frequently Asked Questions
Why does the formula leave repeated group-code rows blank?
The COUNT(SEARCH(...)) portion checks the current code against all earlier destination cells in column A. Only the first occurrence produces a count of 1, so later occurrences return the empty string "".
How do I change the formula for codes longer than three characters?
Replace every instance of LEFT(...,3) with the required length, such as LEFT($A2,4) and LEFT($G$2:$G$8,4) for four-character codes. Use the same length in both places.
Do I need Ctrl+Shift+Enter for this formula in Excel?
Microsoft 365, Excel for the web, and Excel 2021 generally accept the formula with Enter. In older desktop Excel versions, use Ctrl+Shift+Enter because the MATCH function evaluates an array created by LEFT($G$2:$G$8,3).
What should I check if the formula returns an error instead of a value?
Verify that the first three characters of the destination code in column A exist in the source-code range G2:G8, and ensure G2:G8 and H2:H8 have matching row counts.
Can I use this formula in WPS Spreadsheets?
Yes. Open the workbook in WPS Spreadsheets, enter the formula in the first output cell, and use the fill handle to apply it to the remaining destination cells.
