How to Mark the Lowest Value for Duplicate Matches in Excel
Question details
The user wants to identify duplicate matches across columns in a spreadsheet and flag the specific row containing the lowest corresponding value.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Filtering and analyzing duplicate records to pinpoint the entry with the minimum value based on a cross-column reference list.
- Observed behavior
- The user requires a formula that outputs 'YES' for the row containing the minimum value among duplicates that exist in a reference column, leaving all other rows blank.
Ensure your data is organized into distinct columns without merged cells, and verify that the column containing the numeric values you want to evaluate is formatted as numbers rather than text.
Use a Nested IF, AND, MINIFS, and MATCH Formula
This solution combines multiple logical and statistical functions to check if a value exists in a reference array and verifies if its corresponding value is the absolute minimum among its duplicates.
The MINIFS function is used to find the lowest value for a specific matching criterion. By wrapping it with IF, AND, and MATCH, you can enforce multiple conditions: the value must appear in your target reference list, and it must be the minimum value for that duplicate set.
Click on the first cell in the column where you want the 'YES' marker to appear (for example, cell C2).
Type the formula: =IF(AND(ISNUMBER(MATCH(A2,$F$2:$F$13,0)),B2=MINIFS($B$2:$B$19,$A$2:$A$19,A2)),"YES","") into the formula bar.
Press Enter to calculate the result for the first row.
Click the small square at the bottom-right corner of the cell (the fill handle) and drag it down to apply the formula to the rest of your dataset.
Highlight Lowest Duplicate Values Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical and statistical functions like MINIFS, MATCH, and IF. You can seamlessly apply complex formulas to filter, highlight, and manage duplicate data without any compatibility issues.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing your duplicate data.
- 2. Select the output cell: Click on the blank cell where you want to display the 'YES' indicator.
- 3. Input the formula: Paste your combined IF, AND, MINIFS, and MATCH formula into the formula bar and press Enter.
- 4. Drag to fill: Double-click or drag the fill handle downwards to automatically evaluate the remaining rows.
Frequently Asked Questions
Why does the MINIFS function return a #NAME? error?
The #NAME? error usually occurs if you are using an older version of Excel (like Excel 2016 or earlier) that does not support the MINIFS function, or if the function name is misspelled. Consider upgrading your software or using an alternative array formula if you are on an older version.
How can I highlight the entire row instead of outputting YES?
You can use Conditional Formatting. Select your entire data range, go to Home > Conditional Formatting > New Rule, choose 'Use a formula to determine which cells to format', and enter the logical portion of the formula: =AND(ISNUMBER(MATCH($A2,$F$2:$F$13,0)),$B2=MINIFS($B$2:$B$19,$A$2:$A$19,$A2)). Then, choose your desired fill color.
Can I mark the highest value instead of the lowest among duplicates?
Yes, you can easily modify the formula to find the highest value by replacing the MINIFS function with MAXIFS. The rest of the logical structure and cell references will remain exactly the same.
