COUNTIFS VLOOKUP 중복 0 처리로 첫 번째 값만 불러오는 엑셀 수식 완전 정리
엑셀에서 VLOOKUP으로 값을 불러올 때 같은 코드가 여러 번 반복되는 목록이면, 똑같은 값이 줄줄이 반복돼서 분석이나 합계 계산이 꼬이기 쉽습니다.
이 글에서는 COUNTIFS와 VLOOKUP을 조합해서 첫 번째 행만 VLOOKUP 결과를 표시하고, 나머지 중복 행은 전부 0으로 처리하는 수식 패턴을 정리합니다.
실제 재고·매출 데이터 예제를 기준으로,
- Quick Fix 한 줄 수식
- 단일 조건 / 다중 조건(상품코드 + 색상)
- 0 vs 빈칸의 차이
- 자주 나오는 오류와 해결법
까지 한 번에 정리해 두면, 이후 비슷한 파일을 만들 때 매번 검색하지 않고 그대로 가져다 쓸 수 있습니다.
Quick Fix: COUNTIFS VLOOKUP 중복 0 처리 핵심 수식
예제 데이터 구조 살펴보기
시트 이름: 판매내역
열 구성:
| 열 | 내용 | 예시 |
|---|---|---|
| A | 상품코드 | P001, P002… |
| B | 수량 | 1, 3, 5 … |
| C | 단가(첫 번째만 표시) | (우리가 계산) |
| D | 금액 | =B * C |
같은 상품코드(P001)가 여러 줄에 있을 수 있고, 단가 기준표는 오른쪽에 별도 영역으로 둡니다.
단가 기준표 (예: F:G열)
| 행 | F(상품코드) | G(기준단가) |
|---|---|---|
| 1 | 상품코드 | 기준단가 |
| 2 | P001 | 10000 |
| 3 | P002 | 12000 |
| 4 | P003 | 9000 |
핵심 수식 한 줄로 끝내기
C2 셀에 아래 수식을 입력한 뒤, 필요한 만큼 아래로 채웁니다.
=IF(
COUNTIFS($A$2:A2, A2) > 1,
0,
VLOOKUP(A2, $F$2:$G$4, 2, FALSE)
)
의미 정리:
COUNTIFS($A$2:A2, A2)→ A2부터 현재 행까지 범위에서 “지금 상품코드(A2)”가 몇 번 나왔는지 센다.- 첫 번째 등장: 1, 두 번째: 2, 세 번째: 3 …
COUNTIFS(...) > 1→ “이 행은 두 번째 이상 등장한 중복이다”라는 뜻IF( COUNTIFS(...) > 1, 0, VLOOKUP(...) )→ 중복이면 0, 아니면 VLOOKUP 결과(단가)를 표시
따라서 각 상품코드의 첫 행만 단가가 들어가고, 이후 중복 행은 모두 0이 됩니다.
단계별 적용 요약
- A열에 상품코드, B열에 수량 입력
- 오른쪽 F:G열에 상품코드별 기준 단가 테이블 준비
C2에 위 수식을 입력하고 아래로 복사D2에는=B2*C2입력 후 아래로 복사SUM(D2:D6)등으로 합계를 구하면, 각 상품코드당 첫 번째 행만 금액이 반영된 합계를 얻을 수 있음
왜 VLOOKUP만으로는 중복을 제어하기 어려운가
VLOOKUP 기본 동작 원리
VLOOKUP은 “찾을 값(lookup_value)”을 기준으로 참조 범위의 왼쪽 첫 열에서 일치하는 값을 찾은 뒤, 오른쪽 열의 값을 가져오는 함수입니다.
가장 단순한 형태는 아래와 같습니다.
=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
중요한 특징은:
lookup_value가 같은 행이 여러 개 있어도, 항상 “처음 찾은 행”의 값을 가져온다는 것- 판매내역처럼 같은 상품코드가 여러 줄 있는 목록에 VLOOKUP을 그냥 쓰면, 각 행마다 동일한 단가가 반복
- 나중에 합계를 내면 중복된 금액까지 모두 더해지는 문제 발생
COUNTIFS로 “지금이 몇 번째 행인지” 세는 이유
COUNTIFS는 여러 조건을 만족하는 셀의 개수를 세는 함수입니다.
=COUNTIFS(범위1, 조건1, 범위2, 조건2, ...)
여기서는 범위1을 A열의 위에서부터 현재 행까지로 잡고, 조건을 현재 상품코드와 같게 두면
=COUNTIFS($A$2:A2, A2)
- 첫 등장 행 → 1
- 두 번째 등장 행 → 2
- 세 번째 등장 행 → 3 …
이렇게 “현재 행이 몇 번째 등장인지”를 숫자로 판단할 수 있습니다. 이 값을 기준으로
- 1일 때만 VLOOKUP 결과 사용
- 2 이상이면 0(또는 빈칸)으로 처리
라는 조건을 걸면 중복 통제가 가능합니다.
실무 예제 1: 상품코드별 첫 번째 단가만 가져오고 나머지는 0 처리
샘플 데이터 표 만들기
① 판매내역 (A:D열)
| 행 | A(상품코드) | B(수량) | C(단가_첫행만) | D(금액) |
|---|---|---|---|---|
| 1 | 상품코드 | 수량 | 단가(첫 행만) | 금액 |
| 2 | P001 | 3 | (수식) | (수식) |
| 3 | P001 | 2 | (수식) | (수식) |
| 4 | P002 | 5 | (수식) | (수식) |
| 5 | P001 | 1 | (수식) | (수식) |
| 6 | P003 | 4 | (수식) | (수식) |
② 단가 기준표 (F:G열)
| 행 | F(상품코드) | G(기준단가) |
|---|---|---|
| 1 | 상품코드 | 기준단가 |
| 2 | P001 | 10000 |
| 3 | P002 | 12000 |
| 4 | P003 | 9000 |
COUNTIFS + VLOOKUP 수식 적용
C2셀 클릭- 아래 수식 입력:
=IF(
COUNTIFS($A$2:A2, A2) > 1,
0,
VLOOKUP(A2, $F$2:$G$4, 2, FALSE)
)
Enter후,C2를 아래 행까지 자동 채우기
예상 결과:
| 행 | A(상품코드) | B(수량) | C(단가_첫행만) | 설명 |
|---|---|---|---|---|
| 2 | P001 | 3 | 10000 | P001 첫 등장 → 단가 |
| 3 | P001 | 2 | 0 | P001 두 번째 → 0 |
| 4 | P002 | 5 | 12000 | P002 첫 등장 → 단가 |
| 5 | P001 | 1 | 0 | P001 세 번째 → 0 |
| 6 | P003 | 4 | 9000 | P003 첫 등장 → 단가 |
D2에는 금액 수식을 입력합니다.
=B2*C2
아래로 복사하면, P001 단가는 맨 윗줄 한 번만 반영되고 나머지는 모두 0이라 전체 합계는 “상품코드별로 단 한 번만 단가를 적용한 금액”이 됩니다.
실무 예제 2: 상품코드 + 색상 조합 기준으로 중복을 0 처리
다중 조건 COUNTIFS 패턴
데이터 구조:
| 열 | 내용 |
|---|---|
| A | 상품코드 |
| B | 색상 |
| C | 수량 |
| D | 단가(첫 조합만) |
| E | 금액 |
“상품코드 + 색상 조합”이 처음 등장한 행만 단가를 가져오고, 나머지 중복 조합은 0으로 처리하려면 COUNTIFS에 조건을 두 개 넣습니다.
D2 수식 예:
=IF(
COUNTIFS($A$2:A2, A2, $B$2:B2, B2) > 1,
0,
VLOOKUP(A2, $H$2:$I$10, 2, FALSE)
)
색상별 첫 주문만 집계하는 예제
| 행 | A(코드) | B(색상) | C(수량) | D(단가_첫 조합만) | E(금액) |
|---|---|---|---|---|---|
| 2 | P001 | BLACK | 3 | (수식) | (수식) |
| 3 | P001 | BLACK | 1 | (수식) | (수식) |
| 4 | P001 | WHITE | 2 | (수식) | (수식) |
| 5 | P001 | BLACK | 4 | (수식) | (수식) |
위 수식을 적용하면 P001+BLACK 조합은 2행에서만 단가가 들어가고 3·5행은 0, P001+WHITE는 4행에만 단가가 들어가 색상 조합별로 첫 주문만 금액이 반영됩니다.
빈칸 “” vs 0, 무엇을 써야 할까?
0을 쓰면 좋은 경우
- 이 열을 계산(합계, 평균, 다른 수식 등)에 그대로 활용할 때
- 중복이 0으로 명확하게 보이는 게 좋을 때
- 피벗테이블에서 값이 있는 행만 필터링하지 않고 바로 합계를 쓰고 싶을 때
핵심 패턴:
=IF(
COUNTIFS($A$2:A2, A2) > 1,
0,
VLOOKUP(A2, $F$2:$G$6, 2, FALSE)
)
빈칸이 더 깔끔한 경우
보고용 표처럼 눈에 보이는 깔끔함이 중요한 시트에서는 0이 아니라 빈칸이 더 좋을 때가 많습니다.
=IF(
COUNTIFS($A$2:A2, A2) > 1,
"",
VLOOKUP(A2, $F$2:$G$6, 2, FALSE)
)
자주 나오는 오류와 Troubleshooting 표
| 증상 | 원인 | 해결법 |
|---|---|---|
| 모든 행이 0으로 나오거나, 모두 단가가 채워짐 | COUNTIFS 범위 고정이 잘못됨 | 첫 번째 범위는 $A$2:A2 형식으로 시작행만 절대참조 |
| 첫 번째 행도 0이 되어버림 | > 1 대신 >= 1 사용 |
중복 조건은 > 1로 사용 |
VLOOKUP에서 #N/A 에러 발생 |
기준표에 없는 상품코드, 공백/서식 차이 | 기준표 데이터 확인, 공백 제거, 숫자/텍스트 형식 통일 |
| 데이터가 많아질수록 파일이 느려짐 | 수천 건 이상에서 COUNTIFS가 많이 반복 | 피벗테이블, 중복 제거 기능, Power Query, UNIQUE 함수 등과 병행 |
| 다중 조건 COUNTIFS 결과가 원하는 것과 다르게 나옴 | 범위와 조건값 범위가 섞이거나 길이가 다름 | COUNTIFS 각 범위의 시작행·끝행이 같은지 재확인 |
다른 방법과의 비교: 중복 제거 기능, 피벗, UNIQUE 함수
COUNTIFS + VLOOKUP 패턴은 원본 데이터를 그대로 두고, 수식으로만 중복을 제어한다는 점이 강점입니다. 하지만 항상 이 방법이 최선은 아닙니다.
데이터 탭의 ‘중복된 항목 제거’
- 원본 데이터에서 중복 행을 직접 삭제
- 매우 빠르고 직관적이지만, 원본 로그를 유지해야 하는 경우에는 부적합
피벗테이블
- 상품코드별, 코드+색상별 집계 결과만 필요하다면 피벗테이블이 더 간단
- 행 필드에 상품코드와 색상을 넣고 값 필드에 수량/금액을 설정하면 중복 없이 합계 가능
UNIQUE 함수
=UNIQUE(범위)로 고유값 목록만 추출- 이 고유값 목록을 기준으로 VLOOKUP이나 XLOOKUP을 적용하면 중복 없는 결과 테이블을 만들 수 있음
- 단, 버전 호환성 문제로 모든 사용자에게 배포하는 파일이라면 COUNTIFS 패턴이 더 안전
마무리 정리 & 함께 보면 좋은 글
- VLOOKUP만 쓰면 중복된 키가 있을 때 모든 행에 같은 값이 반복됨
- COUNTIFS로 “현재 행이 몇 번째 등장인지”를 세어 1일 때만 VLOOKUP 실행, 2 이상이면 0 또는 빈칸 처리
- 다중 조건이 필요할 때는 COUNTIFS에 조건을 늘려 사용
- 0을 쓰면 합계·평균 등 숫자 계산에 유리, 빈칸은 보고용 표에서 더 깔끔
- 데이터가 매우 클 때는 피벗, 중복 제거, UNIQUE, Power Query 등과 병행
함께 보면 좋은 exceljump 글:
- VLOOKUP 기본 사용법과 자주 나오는 오류 정리
- COUNTIFS 함수로 여러 조건 개수 세기
- 중복 데이터 깔끔 정리: UNIQUE + FILTER 실전 예제
- SUMIFS로 상품·기간별 매출 자동 집계하기
- 엑셀 재고관리 템플릿 만들기