VLOOKUP 단가가 틀릴 때: 거래처·상품·적용일 기준 최신 단가로 정산 검산하기

정산표를 맞추다 보면 VLOOKUP 단가가 틀림, 최신 단가가 안 잡힘, 거래처별 단가 적용 오류 같은 문제가 꽤 자주 나옵니다. 특히 같은 상품코드라도 거래처마다 단가가 다르고, 월 중간에 단가가 바뀌는 파일에서는 단순 VLOOKUP으로는 거의 한 번쯤 사고가 납니다.

오늘 예제는 주문원장에 들어온 정산서 단가가 맞는지, 별도 단가이력표를 기준으로 한 줄씩 검산하는 상황입니다. 핵심은 거래처 + 상품코드 + 주문일 이전의 가장 최근 적용일을 찾아서 단가를 가져오는 것입니다.

Q. 왜 VLOOKUP으로 상품코드만 찾으면 단가가 틀리나요?

VLOOKUP은 기본적으로 찾을 값을 왼쪽 첫 열에서 찾아 첫 번째로 만나는 값을 가져옵니다. 그런데 단가이력표에는 같은 상품코드가 여러 번 등장합니다. 거래처가 다르거나, 적용시작일이 다르기 때문입니다.

예를 들어 A거래처의 P-100 상품은 7월 1일부터 12,000원이고, 7월 15일부터 12,500원으로 바뀌었다고 해보겠습니다. 7월 20일 주문에는 12,500원이 적용되어야 하는데, VLOOKUP이 위쪽의 12,000원을 먼저 만나면 그대로 잘못 가져옵니다.

Q. 예제 표는 어떻게 생겼나요?

주문원장은 같은 시트의 A5:J304 범위에 있다고 가정하겠습니다. A열은 주문일, B열은 거래처, C열은 상품코드, D열은 수량, E열은 정산서단가, F열은 정산서금액입니다. G열부터 J열은 우리가 검산용으로 추가할 열입니다.

단가이력표는 같은 시트의 M4:Q120 범위에 둡니다. M열은 적용시작일, N열은 거래처, O열은 상품코드, P열은 단가, Q열은 비고입니다.

Q. 수식 넣기 전에 꼭 해야 할 정렬이 있나요?

있습니다. 이번 방식은 단가이력표를 적용시작일 기준 내림차순으로 정렬해야 안정적으로 작동합니다. 즉 최신 날짜가 위로 오게 해야 합니다.

정렬 범위는 M4:Q120 전체입니다. M열만 따로 정렬하면 거래처, 상품코드, 단가가 서로 엇갈리므로 절대 안 됩니다. 단가이력표를 선택한 뒤, M열 적용시작일을 기준으로 최신순 정렬하세요.

Q. 최신 적용일 기준 단가는 어떤 수식으로 가져오나요?

G5 셀에 검산단가를 구하겠습니다. 조건은 세 가지입니다. N열 거래처가 B5와 같고, O열 상품코드가 C5와 같고, M열 적용시작일이 A5 주문일보다 작거나 같아야 합니다.

단가이력표가 최신순으로 정렬되어 있다면, 이 조건을 만족하는 첫 번째 행이 곧 그 주문일에 적용할 최신 단가입니다. Microsoft 365 또는 XLOOKUP을 사용할 수 있는 버전이라면 아래 수식을 G5에 입력한 뒤 G304까지 복사합니다.

=IFERROR(XLOOKUP(1,($N$5:$N$120=B5)*($O$5:$O$120=C5)*($M$5:$M$120<=A5),$P$5:$P$120,"단가없음",0),"단가없음")

여기서 바꿔야 할 부분은 범위입니다. 내 파일의 단가이력표가 500행까지 있다면 $N$5:$N$120을 $N$5:$N$500처럼 모두 같은 행 번호로 늘려야 합니다. 한 범위만 늘리고 다른 범위는 그대로 두면 수식 오류가 나거나 엉뚱한 결과가 나옵니다.

Q. XLOOKUP이 없는 파일에서는 어떻게 하나요?

XLOOKUP을 못 쓰는 환경이라면 INDEX와 MATCH 조합으로 처리할 수 있습니다. 같은 논리로 조건을 곱해서 1이 되는 첫 번째 행을 찾고, 그 위치의 P열 단가를 가져옵니다.

G5에 아래 수식을 입력합니다. 예전 버전에서는 입력 후 Ctrl + Shift + Enter로 확정해야 할 수 있습니다. 정상 입력되면 수식 앞뒤에 중괄호가 보일 수 있는데, 직접 중괄호를 타이핑하는 것은 아닙니다.

=IFERROR(INDEX($P$5:$P$120,MATCH(1,($N$5:$N$120=B5)*($O$5:$O$120=C5)*($M$5:$M$120<=A5),0)),"단가없음")

이 수식도 단가이력표가 최신순으로 정렬되어 있어야 합니다. 정렬이 오래된 날짜순이면 조건에 맞는 첫 번째 값이 과거 단가가 되어 버립니다. 이 부분이 실무에서 가장 많이 놓치는 포인트입니다.

Q. 정산서 금액과 비교하려면 어떤 열을 추가하나요?

G열에 검산단가가 들어갔다면, H열에는 검산금액을 계산합니다. H5 셀에 아래 수식을 넣고 H304까지 복사하세요.

=IFERROR(ROUND(D5*G5,0),"")

D5는 수량, G5는 검산단가입니다. ROUND를 넣은 이유는 할인율이나 단가 계산 과정에서 소수점이 섞인 파일을 다룰 때 1원 차이를 줄이기 위해서입니다. 원 단위 정산이면 0자리 반올림이 가장 흔합니다.

I열에는 정산서금액과 검산금액의 차이를 표시합니다. I5 셀에는 아래 수식을 입력합니다.

=IFERROR(F5-H5,"")

J열에는 판정을 넣습니다. 단가가 없으면 단가이력 누락, 차이가 0이면 정상, 아니면 확인 필요로 표시합니다.

=IF(G5="단가없음","단가이력 누락",IF(I5=0,"정상","확인 필요"))

Q. 월별로 차이 금액이 얼마나 되는지 바로 확인할 수 있나요?

가능합니다. 예를 들어 S2 셀에 기준월을 2026-07-01 형태로 입력하고, T2 셀에 확인할 거래처명을 입력한다고 하겠습니다. U2 셀에는 해당 월, 해당 거래처의 차이금액 합계를 구합니다.

=SUMIFS($I$5:$I$304,$A$5:$A$304,">="&$S$2,$A$5:$A$304,"<="&EOMONTH($S$2,0),$B$5:$B$304,$T$2)

이 수식은 I열 차이금액을 합산하되, 주문일이 S2 기준월의 1일부터 말일까지이고, 거래처가 T2와 같은 행만 더합니다. 결과가 0이면 금액상으로는 맞는 상태입니다. 다만 단가이력 누락이 있는데 우연히 차이가 0인 경우도 있으니 J열 판정도 같이 확인해야 합니다.

누락 건수는 COUNTIFS로 셀 수 있습니다. V2 셀에 아래 수식을 넣으면 해당 월, 해당 거래처의 단가이력 누락 건수가 나옵니다.

=COUNTIFS($A$5:$A$304,">="&$S$2,$A$5:$A$304,"<="&EOMONTH($S$2,0),$B$5:$B$304,$T$2,$J$5:$J$304,"단가이력 누락")

Q. 적용시작일이 20260701처럼 문자로 들어오면요?

단가이력표의 M열 적용시작일이 날짜가 아니라 20260701 같은 숫자 또는 문자로 들어오면 비교 조건이 꼬일 수 있습니다. 이때는 보조열을 하나 만들어 날짜로 바꾸는 편이 안전합니다.

예를 들어 R열을 적용일변환으로 사용하고, R5 셀에 아래 수식을 넣습니다. M5 값이 20260715처럼 들어온 상황입니다.

=DATE(LEFT(M5,4),MID(M5,5,2),RIGHT(M5,2))

이후 검산단가 수식에서 $M$5:$M$120 대신 $R$5:$R$120을 사용하면 됩니다. 단, 정렬도 R열 적용일변환 기준 최신순으로 해야 합니다. 원본 M열이 문자 날짜인 상태에서 그냥 정렬하면 2026-7-5와 2026-7-15의 순서가 이상하게 잡힐 수 있습니다.

Q. 결과가 이상할 때 어디부터 확인해야 하나요?

첫 번째는 단가이력표 정렬입니다. M4:Q120 또는 R4:Q120 전체 범위를 잡고 최신순으로 정렬했는지 확인하세요. 단가표 일부 열만 정렬하면 그때부터 검산 결과는 믿기 어렵습니다.

두 번째는 거래처명과 상품코드가 정확히 같은지 확인합니다. 눈으로 보기에는 같아도 뒤에 공백이 붙어 있거나, 상품코드에 하이픈이 빠져 있으면 조건이 맞지 않습니다. 이런 경우 J열에 단가이력 누락이 많이 뜹니다.

세 번째는 날짜 형식입니다. 주문원장 A열과 단가이력 M열 또는 R열이 실제 날짜인지 확인해야 합니다. 셀 표시만 날짜처럼 보이고 실제로는 문자이면, 작거나 같다는 비교가 의도대로 되지 않을 수 있습니다.

네 번째는 범위 행 번호입니다. XLOOKUP 또는 INDEX/MATCH 수식에서 $N$5:$N$120, $O$5:$O$120, $M$5:$M$120, $P$5:$P$120의 시작 행과 끝 행이 모두 같아야 합니다. 하나라도 다르면 결과가 틀어집니다.

Q. 매달 같은 작업이면 VBA로 정렬과 수식 입력을 한 번에 할 수 있나요?

주문원장 행이 매달 300행, 1,000행씩 늘어나고 단가이력표도 계속 바뀐다면 버튼용 매크로로 만들어 두는 것도 좋습니다. 아래 코드는 현재 활성 시트에서 단가이력표 M4:Q120을 적용시작일 최신순으로 정렬하고, 주문원장 A5:J304의 G:J열에 검산 수식을 채웁니다.

실행 전에는 파일을 반드시 복사본으로 저장하세요. 매크로 실행은 일반 실행취소가 되지 않습니다. 범위가 다르면 코드의 304, 120 숫자를 내 파일에 맞게 바꿔야 합니다.

Sub 단가검산_정렬_수식입력()
    Dim ws As Worksheet
    Dim r As Long
    Set ws = ActiveSheet

    If MsgBox("현재 시트의 단가이력표를 정렬하고 검산 수식을 입력합니다. 계속할까요?", vbYesNo) <> vbYes Then Exit Sub

    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=ws.Range("M5:M120"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ws.Sort
        .SetRange ws.Range("M4:Q120")
        .Header = xlYes
        .Apply
    End With

    For r = 5 To 304
        ws.Cells(r, "G").FormulaArray = "=IFERROR(INDEX($P$5:$P$120,MATCH(1,($N$5:$N$120=B" & r & ")*($O$5:$O$120=C" & r & ")*($M$5:$M$120<=A" & r & "),0)),""단가없음"")"
        ws.Cells(r, "H").Formula = "=IFERROR(ROUND(D" & r & "*G" & r & ",0),"""")"
        ws.Cells(r, "I").Formula = "=IFERROR(F" & r & "-H" & r & ","""")"
        ws.Cells(r, "J").Formula = "=IF(G" & r & "=""단가없음"",""단가이력 누락"",IF(I" & r & "=0,""정상"",""확인 필요""))"
    Next r

    MsgBox "단가 검산 수식 입력이 끝났습니다."
End Sub

되돌리고 싶다면 매크로 실행 전 저장해 둔 복사본으로 돌아가는 것이 가장 안전합니다. 또는 실행 직후 파일을 저장하지 말고 닫은 뒤, 저장하지 않음을 선택하면 실행 전 상태로 돌아갈 수 있습니다.

Q. 이 방식은 어떤 업무에 쓰면 좋나요?

같은 상품코드가 여러 거래처에 팔리고, 단가가 기간별로 바뀌는 정산 업무라면 이 방식이 잘 맞습니다. 예를 들면 유통사 납품 단가, 대리점별 공급가, 행사 적용 단가, 월 중간 가격 변경 검산에 바로 응용할 수 있습니다.

반대로 상품코드 하나에 단가가 항상 하나뿐이고 기간 변경도 없다면 단순 VLOOKUP으로도 충분합니다. 하지만 거래처, 상품, 적용일 중 하나라도 조건이 붙는 순간에는 단일 키 VLOOKUP보다 조건 조합 수식으로 검산하는 편이 안전합니다. 이럴 때는 단가이력표를 최신순으로 정렬하고, XLOOKUP 또는 INDEX/MATCH로 “주문일 이전의 최신 단가”를 가져오면 됩니다.