Complete Guide to Using COUNTIFS and VLOOKUP to Return Only the First Value and Set Duplicates to 0
When you use VLOOKUP in Excel to retrieve values from a list where the same code appears multiple times, the same value can be repeated down the rows, making analysis and total calculations difficult.
This article covers a formula pattern that combines COUNTIFS and VLOOKUP to display the VLOOKUP result only in the first row and set all remaining duplicate rows to 0.
Using practical inventory and sales data examples, this guide covers:
- A one-line Quick Fix formula
- Single criteria and multiple criteria (product code + color)
- The difference between 0 and blank cells
- Common errors and solutions
Once you have this organized, you can reuse it in similar workbooks without having to search for it every time.
Quick Fix: Core COUNTIFS and VLOOKUP Formula for Setting Duplicates to 0
Example Data Structure
Worksheet name: 판매내역
Column layout:
| Column | Contents | Example |
|---|---|---|
| A | Product Code | P001, P002… |
| B | Quantity | 1, 3, 5 … |
| C | Unit Price (first occurrence only) | (calculated) |
| D | Amount | =B * C |
The same product code (P001) can appear in multiple rows, and the unit price reference table is placed in a separate area on the right.
Unit Price Reference Table (for example, columns F:G)
| Row | F(Product Code) | G(Standard Unit Price) |
|---|---|---|
| 1 | Product Code | Standard Unit Price |
| 2 | P001 | 10000 |
| 3 | P002 | 12000 |
| 4 | P003 | 9000 |
The Core Formula in One Line
Enter the following formula in cell C2, then fill it down as needed.
=IF(
COUNTIFS($A$2:A2, A2) > 1,
0,
VLOOKUP(A2, $F$2:$G$4, 2, FALSE)
)
What it means:
COUNTIFS($A$2:A2, A2)→ Counts how many times the current product code (A2) appears in the range from A2 through the current row.- First occurrence: 1; second occurrence: 2; third occurrence: 3 …
COUNTIFS(...) > 1→ Means that this row is a duplicate that appears for the second time or later.IF( COUNTIFS(...) > 1, 0, VLOOKUP(...) )→ Displays 0 for duplicates; otherwise, displays the VLOOKUP result (unit price).
As a result, only the first row for each product code displays the unit price, and all later duplicate rows display 0.
Step-by-Step Application Summary
- Enter product codes in column A and quantities in column B.
- Prepare a reference table of standard unit prices by product code in columns F:G on the right.
- Enter the formula above in
C2and copy it down. - Enter
=B2*C2inD2and copy it down. - When you calculate a total with
SUM(D2:D6), only the first row for each product code is included in the amount total.
Why VLOOKUP Alone Cannot Easily Control Duplicates
How VLOOKUP Works
VLOOKUP is a function that finds a match for a lookup value in the leftmost column of a reference range and returns a value from a column to the right.
Its simplest form is:
=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
Important characteristics:
- Even if multiple rows contain the same
lookup_value, it always returns the value from the first match it finds. - If you use VLOOKUP directly in a list such as sales records where the same product code appears in multiple rows, the same unit price is repeated for every row.
- When you calculate totals later, the duplicated amounts are included as well.
Why Use COUNTIFS to Count the Current Occurrence
COUNTIFS is a function that counts cells meeting multiple criteria.
=COUNTIFS(범위1, 조건1, 범위2, 조건2, ...)
Here, if you set the first range to column A from the top through the current row and set the criterion to the current product code:
=COUNTIFS($A$2:A2, A2)
- First occurrence → 1
- Second occurrence → 2
- Third occurrence → 3 …
This lets you determine which occurrence the current row represents. Based on this value, you can set conditions to:
- Use the VLOOKUP result only when the count is 1.
- Return 0 (or a blank) when the count is 2 or greater.
This makes duplicate control possible.
Practical Example 1: Return the First Unit Price for Each Product Code and Set the Rest to 0
Create the Sample Data Tables
① Sales Records (columns A:D)
| Row | A(Product Code) | B(Quantity) | C(Unit Price_First Row Only) | D(Amount) |
|---|---|---|---|---|
| 1 | Product Code | Quantity | Unit Price (first row only) | Amount |
| 2 | P001 | 3 | (formula) | (formula) |
| 3 | P001 | 2 | (formula) | (formula) |
| 4 | P002 | 5 | (formula) | (formula) |
| 5 | P001 | 1 | (formula) | (formula) |
| 6 | P003 | 4 | (formula) | (formula) |
② Unit Price Reference Table (columns F:G)
| Row | F(Product Code) | G(Standard Unit Price) |
|---|---|---|
| 1 | Product Code | Standard Unit Price |
| 2 | P001 | 10000 |
| 3 | P002 | 12000 |
| 4 | P003 | 9000 |
Apply the COUNTIFS + VLOOKUP Formula
- Click cell
C2. - Enter the following formula:
=IF(
COUNTIFS($A$2:A2, A2) > 1,
0,
VLOOKUP(A2, $F$2:$G$4, 2, FALSE)
)
- Press
Enter, then fillC2down through the remaining rows.
Expected results:
| Row | A(Product Code) | B(Quantity) | C(Unit Price_First Row Only) | Explanation |
|---|---|---|---|---|
| 2 | P001 | 3 | 10000 | First occurrence of P001 → unit price |
| 3 | P001 | 2 | 0 | Second occurrence of P001 → 0 |
| 4 | P002 | 5 | 12000 | First occurrence of P002 → unit price |
| 5 | P001 | 1 | 0 | Third occurrence of P001 → 0 |
| 6 | P003 | 4 | 9000 | First occurrence of P003 → unit price |
Enter the amount formula in D2.
=B2*C2
After copying it down, the P001 unit price is included only once in the top row, while all other occurrences are 0. The overall total therefore applies the unit price only once per product code.
Practical Example 2: Set Duplicates to 0 Based on a Product Code + Color Combination
Multiple-Criteria COUNTIFS Pattern
Data structure:
| Column | Contents |
|---|---|
| A | Product Code |
| B | Color |
| C | Quantity |
| D | Unit Price (first combination only) |
| E | Amount |
To return the unit price only in the first row where a “product code + color combination” appears and set later duplicate combinations to 0, use two criteria in COUNTIFS.
Example formula for D2:
=IF(
COUNTIFS($A$2:A2, A2, $B$2:B2, B2) > 1,
0,
VLOOKUP(A2, $H$2:$I$10, 2, FALSE)
)
Example: Count Only the First Order for Each Color
| Row | A(Code) | B(Color) | C(Quantity) | D(Unit Price_First Combination Only) | E(Amount) |
|---|---|---|---|---|---|
| 2 | P001 | BLACK | 3 | (formula) | (formula) |
| 3 | P001 | BLACK | 1 | (formula) | (formula) |
| 4 | P001 | WHITE | 2 | (formula) | (formula) |
| 5 | P001 | BLACK | 4 | (formula) | (formula) |
When you apply the formula above, the P001+BLACK combination receives the unit price only in row 2, while rows 3 and 5 return 0. P001+WHITE receives the unit price only in row 4, so the amount is included only for the first order of each color combination.
Blank “” vs. 0: Which Should You Use?
When 0 Is Better
- When you will use this column directly in calculations such as totals, averages, or other formulas.
- When you want duplicates to be clearly visible as 0.
- When you want to use totals directly in a PivotTable without filtering for rows that contain values.
Core pattern:
=IF(
COUNTIFS($A$2:A2, A2) > 1,
0,
VLOOKUP(A2, $F$2:$G$6, 2, FALSE)
)
When a Blank Is Cleaner
For worksheets where a clean visual presentation matters, such as reports, a blank is often preferable to 0.
=IF(
COUNTIFS($A$2:A2, A2) > 1,
"",
VLOOKUP(A2, $F$2:$G$6, 2, FALSE)
)
Common Errors and Troubleshooting Table
| Symptom | Cause | Solution |
|---|---|---|
| Every row returns 0, or every row displays a unit price | The COUNTIFS range is anchored incorrectly | For the first range, use the $A$2:A2 format so only the starting row is an absolute reference |
| The first row also returns 0 | >= 1 is used instead of > 1 |
Use > 1 for the duplicate condition |
A #N/A error occurs in VLOOKUP |
The product code is not in the reference table, or there are spaces or formatting differences | Check the reference table data, remove spaces, and make number/text formats consistent |
| The workbook becomes slower as data grows | COUNTIFS is repeatedly calculated across thousands of rows | Also use PivotTables, Remove Duplicates, Power Query, the UNIQUE function, and similar tools |
| Multiple-criteria COUNTIFS results differ from expectations | The ranges and criteria ranges are mixed up or have different lengths | Double-check that every COUNTIFS range has the same starting and ending rows |
Comparison With Other Methods: Remove Duplicates, PivotTables, and UNIQUE
The COUNTIFS + VLOOKUP pattern is useful because it keeps the original data intact and controls duplicates using formulas only. However, it is not always the best method.
Remove Duplicates on the Data Tab
- Directly deletes duplicate rows from the source data.
- It is fast and intuitive, but unsuitable when you need to retain the original log.
PivotTables
- If you only need summarized results by product code or by code + color, a PivotTable is simpler.
- Add product code and color as row fields and set quantity/amount as value fields to total data without duplicates.
UNIQUE Function
- Use
=UNIQUE(범위)to extract a list of unique values. - You can create a results table without duplicates by applying VLOOKUP or XLOOKUP based on this unique-value list.
- However, if the workbook will be distributed to users with different Excel versions, the COUNTIFS pattern is safer for compatibility.
Summary and Related Articles
- When you use VLOOKUP alone and duplicate keys exist, the same value is repeated on every row.
- Use COUNTIFS to count the current occurrence: run VLOOKUP only when the count is 1, and return 0 or a blank when it is 2 or greater.
- When multiple criteria are needed, add criteria to COUNTIFS.
- Using 0 is useful for calculations such as totals and averages, while blanks are cleaner in reporting tables.
- For very large datasets, also consider PivotTables, Remove Duplicates, UNIQUE, and Power Query.
Related ExcelJump articles:
- VLOOKUP Basics and Common Errors
- Count Multiple Criteria with the COUNTIFS Function
- Clean Up Duplicate Data: Practical UNIQUE + FILTER Examples
- Automatically Summarize Sales by Product and Period with SUMIFS
- Create an Excel Inventory Management Template