엑셀 VLOOKUP 단가가 틀릴 때: 거래처별 기간별 단가표를 SUMIFS·COUNTIFS로 복구하는 법
엑셀 VLOOKUP 단가가 틀릴 때 가장 난감한 순간은 수식은 멀쩡히 들어가 있는데 금액만 조용히 틀어지는 경우입니다. 이번에 확인한 파일도 상품코드로 단가를 끌어오고 있었는데, 7월 주문 일부가 6월 단가로 계산되어 정산금액이 맞지 않았습니다.
처음에는 단순히 VLOOKUP 범위가 밀렸거나 절대참조가 빠진 줄 알았습니다. 그런데 표를 열어보니 같은 상품코드라도 거래처별 단가가 다르고, 적용시작일과 적용종료일에 따라 단가가 바뀌는 구조였습니다. 이런 파일은 상품코드 하나만 보고 찾으면 거의 반드시 오류가 납니다.
주문내역에는 단가가 들어왔는데 정산금액만 ERP와 다르게 나오는 상황
예시 파일의 주문내역 시트는 A열부터 J열까지 사용하고, 실제 데이터는 2행부터 5000행까지 있다고 가정하겠습니다. B열 주문일은 ERP에서 내려받은 값이라 어떤 행은 날짜이고, 어떤 행은 20260703처럼 8자리 텍스트로 들어와 있습니다.
| 열 | 열 이름 | 예시 값 | 설명 |
|---|---|---|---|
| A | 주문번호 | SO-260701-001 | 주문 고유번호 |
| B | 주문일 | 20260703 | 날짜 또는 텍스트 날짜 |
| C | 거래처코드 | 0105 | 앞자리 0 주의 |
| D | 상품코드 | P-1007 | 단가표 조회 기준 |
| E | 수량 | 12 | 판매 수량 |
| F | 기존단가 | 8500 | 기존 VLOOKUP 결과 |
| G | 정상단가 | 새로 계산할 단가 | |
| H | 단가상태 | 단가 없음, 기간 중복 확인 | |
| I | 주문일_정리 | 날짜 변환 보조열 | |
| J | 공급금액 | 수량 × 정상단가 |
단가표 시트는 A열부터 H열까지 사용합니다. 핵심은 A열 거래처코드, B열 상품코드, C열 적용시작일, D열 적용종료일, E열 공급단가입니다. G열과 H열은 날짜를 정리하기 위한 보조열로 추가합니다.
| A 거래처코드 | B 상품코드 | C 적용시작일 | D 적용종료일 | E 공급단가 | 비고 |
|---|---|---|---|---|---|
| 0105 | P-1007 | 20260601 | 20260630 | 8500 | 6월 단가 |
| 0105 | P-1007 | 20260701 | 20260731 | 9200 | 7월 단가 |
| 0105 | P-2001 | 20260701 | 14300 | 종료일 없음 | |
| 0203 | P-1007 | 20260701 | 20260731 | 8800 | 거래처별 단가 |
| 0105 | P-1007 | 20260715 | 20260720 | 9100 | 중복 테스트 |
원인은 VLOOKUP이 아니라 조회 기준이 부족했던 것이었습니다
기존 수식은 주문내역 F2에 아래처럼 들어가 있었습니다. 상품코드만 기준으로 단가표에서 첫 번째 값을 가져오는 방식입니다.
=IFERROR(VLOOKUP(D2,단가표!$B$2:$E$1000,4,0),0)이 수식 자체가 틀린 문법은 아닙니다. 문제는 단가표에 같은 상품코드 P-1007이 여러 번 등장한다는 점입니다. 거래처코드가 다르고, 적용기간도 다른데 VLOOKUP은 조건을 하나만 보고 위에서 처음 만난 값을 가져옵니다.
그래서 2026년 7월 3일 주문인데도 6월 단가 8500원이 들어오거나, 거래처 0203의 단가가 0105 주문에 들어가는 일이 생깁니다. 이럴 때는 찾기 함수 하나로 끝내기보다 조건별 합계 함수인 SUMIFS와 건수 확인용 COUNTIFS를 같이 써서 복구하는 편이 안전합니다.
먼저 주문일과 단가표 날짜를 같은 날짜 형식으로 맞춥니다
SUMIFS에서 날짜 조건을 걸려면 주문일과 적용시작일, 적용종료일이 엑셀이 알아보는 날짜여야 합니다. 겉으로는 20260703처럼 보여도 텍스트면 날짜 비교가 꼬일 수 있습니다.
주문내역 I2에는 주문일을 정리하는 수식을 넣습니다. B2가 이미 날짜면 그대로 쓰고, 8자리 텍스트라면 LEFT, MID, RIGHT로 연·월·일을 잘라 DATE로 다시 조립합니다.
=IFERROR(IF(ISNUMBER(B2),B2,DATE(LEFT(B2,4),MID(B2,5,2),RIGHT(B2,2))),"")이 수식을 I2에 입력한 뒤 I5000까지 내려 채웁니다. I열 표시 형식은 yyyy-mm-dd로 바꿔 두면 눈으로 확인하기 편합니다.
단가표 시트에서도 같은 작업을 합니다. 단가표 G2에는 적용시작일 정리값을 만듭니다.
=IFERROR(IF(ISNUMBER(C2),C2,DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2))),"")단가표 H2에는 적용종료일 정리값을 만듭니다. 종료일이 빈칸인 단가는 계속 적용되는 단가로 보고 2099년 12월 31일을 임시 종료일로 넣었습니다.
=IF(D2="",DATE(2099,12,31),IFERROR(IF(ISNUMBER(D2),D2,DATE(LEFT(D2,4),MID(D2,5,2),RIGHT(D2,2))),""))이 두 수식도 단가표의 마지막 행인 1000행까지 채웁니다. 날짜 정리가 끝나야 뒤에서 단가 없음과 기간 중복을 정확히 잡아낼 수 있습니다.
정상단가는 SUMIFS로 가져오고, 단가상태는 COUNTIFS로 먼저 판정합니다
바로 정상단가부터 계산하고 싶지만, 실무에서는 상태열을 먼저 만드는 것이 좋습니다. 단가가 없어서 0인지, 기간이 겹쳐서 여러 단가가 잡힌 것인지 구분하지 않으면 나중에 검산이 어렵습니다.
주문내역 H2에 단가상태 수식을 넣습니다. 조건은 거래처코드 C2, 상품코드 D2, 주문일_정리 I2입니다. 단가표에서 적용시작일은 주문일보다 작거나 같고, 적용종료일은 주문일보다 크거나 같아야 합니다.
=IF(I2="","주문일 오류",IF(COUNTIFS(단가표!$A$2:$A$1000,$C2,단가표!$B$2:$B$1000,$D2,단가표!$G$2:$G$1000,"<="&$I2,단가표!$H$2:$H$1000,">="&$I2)=0,"단가 없음",IF(COUNTIFS(단가표!$A$2:$A$1000,$C2,단가표!$B$2:$B$1000,$D2,단가표!$G$2:$G$1000,"<="&$I2,단가표!$H$2:$H$1000,">="&$I2)>1,"단가 기간 중복","OK")))H열이 OK인 행만 정상단가를 계산합니다. 주문내역 G2에는 아래 수식을 넣습니다.
=IF($H2="OK",SUMIFS(단가표!$E$2:$E$1000,단가표!$A$2:$A$1000,$C2,단가표!$B$2:$B$1000,$D2,단가표!$G$2:$G$1000,"<="&$I2,단가표!$H$2:$H$1000,">="&$I2),"")여기서 SUMIFS를 쓰는 이유는 조건을 여러 개 걸기 쉽기 때문입니다. 거래처코드, 상품코드, 시작일, 종료일 조건을 모두 만족하는 공급단가만 가져옵니다. 단가상태가 OK라면 조건에 맞는 행이 1개뿐이므로 SUMIFS 결과가 곧 정상단가가 됩니다.
공급금액은 J2에 계산합니다. 정산금액은 원 단위 반올림이 필요한 경우가 많으므로 ROUND를 같이 사용합니다.
=IF($H2="OK",ROUND($E2*$G2,0),"")거래처코드 앞자리 0 때문에 단가 없음이 뜨는 행을 잡습니다
단가표에는 거래처코드가 0105인데 주문내역에는 105로 들어온 경우도 자주 있습니다. 이 경우 눈으로 보면 같은 거래처처럼 보여도 엑셀은 다른 값으로 봅니다.
거래처코드가 항상 4자리라면 주문내역 C열 옆에 확인용 열을 하나 만들고 TEXT로 맞춰볼 수 있습니다. 예를 들어 K2에 정리 거래처코드를 만들려면 아래처럼 입력합니다.
=TEXT(C2,"0000")이후 단가상태 수식의 $C2 부분을 $K2로 바꾸면 앞자리 0 누락 문제를 줄일 수 있습니다. 단, 실제 거래처코드가 5자리, 6자리로 섞여 있는 회사라면 자리수를 마음대로 고정하면 안 됩니다. 먼저 거래처마스터와 코드 규칙을 확인해야 합니다.
거래처명까지 확인하고 싶다면 거래처마스터 시트를 사용합니다. 거래처마스터 A열에는 거래처코드, B열에는 거래처명이 있고 2행부터 200행까지 있다고 가정하겠습니다. 주문내역 L2에 XLOOKUP을 넣으면 코드가 맞는지 바로 볼 수 있습니다.
=IFERROR(XLOOKUP(TEXT($C2,"0000"),거래처마스터!$A$2:$A$200,거래처마스터!$B$2:$B$200),"거래처코드 확인")XLOOKUP을 사용할 수 없는 환경이라면 INDEX와 MATCH 조합으로 같은 결과를 만들 수 있습니다.
=IFERROR(INDEX(거래처마스터!$B$2:$B$200,MATCH(TEXT($C2,"0000"),거래처마스터!$A$2:$A$200,0)),"거래처코드 확인")월별 정산금액이 맞는지 EOMONTH로 다시 검산합니다
행별 단가를 고친 뒤에는 월별 합계가 맞는지 확인해야 합니다. 주문내역 M1에 기준월을 2026-07처럼 입력하고, M3에는 확인할 거래처코드 0105를 입력해 보겠습니다. N3에는 해당 거래처의 7월 공급금액 합계를 계산합니다.
=SUMIFS($J$2:$J$5000,$C$2:$C$5000,$M$3,$I$2:$I$5000,">="&DATE(LEFT($M$1,4),RIGHT($M$1,2),1),$I$2:$I$5000,"<="&EOMONTH(DATE(LEFT($M$1,4),RIGHT($M$1,2),1),0))같은 기준으로 건수도 확인합니다. N4에는 해당 월 주문 건수를 계산합니다.
=COUNTIFS($C$2:$C$5000,$M$3,$I$2:$I$5000,">="&DATE(LEFT($M$1,4),RIGHT($M$1,2),1),$I$2:$I$5000,"<="&EOMONTH(DATE(LEFT($M$1,4),RIGHT($M$1,2),1),0))이 합계와 ERP의 월별 거래처 정산표를 비교합니다. 금액 차이가 남는다면 H열에서 단가 없음, 단가 기간 중복, 주문일 오류가 남아 있는지 먼저 필터로 확인합니다. 수식을 고치기 전에 오류 상태를 먼저 좁히면 훨씬 빠릅니다.
단가 기간 중복이 뜨는 행은 수식 문제가 아니라 단가표 문제입니다
위 예시 단가표에는 일부러 0105, P-1007, 20260715~20260720 행을 하나 더 넣었습니다. 이 상태에서 2026년 7월 16일 주문이 들어오면 기존 7월 단가 기간과 겹치기 때문에 COUNTIFS 결과가 2가 됩니다.
이때 정상단가 G열에 SUMIFS만 단독으로 넣었다면 9200원과 9100원이 더해져 18300원이 나올 수 있습니다. 그래서 상태열을 먼저 만든 것입니다. 단가 기간 중복은 수식으로 억지 처리하기보다 단가표에서 적용종료일을 수정하거나, 우선 적용해야 할 단가 기준을 업무 담당자와 정해야 합니다.
만약 특정 기간의 단가가 잘못 등록됐는지 확인하려면 단가표에서 거래처코드와 상품코드를 필터링한 뒤 적용시작일 오름차순으로 정렬해 보세요. 종료일이 다음 시작일 전날로 끊기는지 보면 대부분의 문제가 바로 보입니다.
다음 정산 때 같은 오류를 막기 위한 체크포인트
거래처별, 상품별, 기간별 단가표를 쓰는 파일에서는 VLOOKUP 하나만으로 단가를 가져오지 않는 편이 안전합니다. 상품코드가 유일값이라는 확신이 있을 때만 VLOOKUP을 쓰고, 기간 조건이 있으면 SUMIFS와 COUNTIFS를 함께 사용하는 흐름으로 바꾸는 것이 좋습니다.
- 주문일, 적용시작일, 적용종료일이 실제 날짜인지 먼저 확인합니다.
- 거래처코드 앞자리 0이 사라졌는지 TEXT로 검산합니다.
- 단가표에 같은 거래처코드와 상품코드가 여러 번 등장하는지 확인합니다.
- COUNTIFS로 조건에 맞는 단가 행이 1개인지 먼저 판정합니다.
- 단가 없음과 기간 중복 행은 금액 계산에서 제외하고 별도로 처리합니다.
- 월별 합계는 DATE와 EOMONTH를 사용해 시작일과 말일 기준으로 다시 검산합니다.
정산 파일에서 가장 위험한 오류는 #N/A처럼 크게 보이는 오류가 아니라, 그럴듯한 숫자가 들어와 조용히 합계를 틀리게 만드는 오류입니다. 단가표가 기간별로 관리되는 구조라면 조회 기준을 하나 더 늘리는 것이 아니라, 거래처·상품·적용기간을 모두 만족하는지 확인하는 방식으로 바꿔야 합니다.