How to Return an XLOOKUP Result Only Once for Duplicate Rows in WPS Spreadsheets
Question details
The user wants to make XLOOKUP return a value only once when consecutive rows contain duplicate lookup values in WPS Spreadsheets.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with rows that contain repeated lookup values and wants to avoid seeing the same XLOOKUP result repeated for every duplicate row.
- Observed behavior
- XLOOKUP returns the same matched value on every row, so duplicate lookup rows all display the result instead of only the first row of each group.
Open the workbook and identify the lookup column and the column or sheet range that contains the values to return. Make sure identical lookup values are arranged in consecutive rows so the IF comparison can detect each duplicate group correctly.
Use IF with XLOOKUP to show the result only on the first duplicate row
Use this method when your data has repeated lookup values in consecutive rows and you want each duplicate group to show the XLOOKUP result only once.
The IF function compares the current lookup value with the value in the previous row. XLOOKUP runs only on the first row of each duplicate group, so later duplicate rows remain blank instead of repeating the same result.
Click the cell in the first data row where the result should appear, such as E2.
Type =IF($D2<>$D1,XLOOKUP($D2,Sheet1!$D:$D,Sheet1!$A:$A,"",0,1),"") and adjust the lookup column, return column, and sheet names to match your workbook.
Press Enter, then drag the fill handle down through all rows that contain lookup values.
Return an XLOOKUP Result Once Per Duplicate Group in WPS Spreadsheets
Use WPS Spreadsheets to combine IF with XLOOKUP and control which rows display lookup results. The formula runs XLOOKUP only when the lookup value changes, so duplicate groups stay clean and your workbook remains compatible with Excel.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the Excel-compatible workbook that contains the duplicate lookup values.
- 2. Select the output cell: Click the first cell where you want the returned XLOOKUP result to appear in the data row.
- 3. Enter the combined formula: Type =IF($D2<>$D1,XLOOKUP($D2,Sheet1!$D:$D,Sheet1!$A:$A,"",0,1),"") and adjust the sheet name and ranges to match your data.
- 4. Fill the formula down: Press Enter, then drag the fill handle down through the remaining rows that should be checked.
Frequently Asked Questions
Why does XLOOKUP repeat the same result for duplicate lookup values?
XLOOKUP finds the matching value for each lookup cell individually, so when consecutive rows contain the same lookup value, each row returns the same match. Wrapping XLOOKUP in an IF comparison makes the formula run only when the value changes from the previous row.
Do the duplicate lookup values need to be sorted for this formula to work?
Yes. The formula compares each row to the row immediately above it, so identical values should be grouped consecutively. If duplicates are scattered, sort the data by the lookup column first or use a different counting formula.
How do I change the formula if my lookup and return data are on another sheet?
Replace Sheet1!$D:$D with the lookup range and Sheet1!$A:$A with the return range on your actual sheet. Keep absolute references with dollar signs so the lookup range does not shift when you fill the formula down.
Can I show the result on the last duplicate row instead of the first?
Yes. Change the comparison to look at the next row instead of the previous row, for example =IF($D2<>$D3,XLOOKUP($D2,Sheet1!$D:$D,Sheet1!$A:$A,"",0,1),"").
What should I do if I need unique results for non-consecutive duplicate groups?
Sort the data so duplicates are consecutive, or add a helper column that creates a unique key for each group. You can then use the same IF and XLOOKUP pattern with the helper column as the lookup value.
