엑셀 주문일 기준 최신 단가가 안 맞을 때: 단가이력표에서 INDEX MATCH·XLOOKUP으로 정확히 찾기

월말 매출 정리에서 은근히 자주 터지는 문제가 있습니다. 주문일은 8월인데 7월 단가가 적용되거나, 거래처별 단가가 다른데 VLOOKUP이 첫 번째 단가만 가져와서 금액이 틀어지는 경우입니다.

특히 단가이력표에 같은 상품코드가 여러 번 나오면 VLOOKUP 안됨, 단가 오류, 금액 합계 안 맞음으로 이어지기 쉽습니다. 이번 글은 주문일 기준으로 그날 적용되어야 할 최신 단가를 찾고, SUMIFS와 COUNTIFS로 검증하는 흐름까지 실무 파일 구조에 맞춰 정리해 보겠습니다.

Q. 왜 VLOOKUP으로 단가를 가져오면 예전 단가가 계속 나오나요?

가장 흔한 원인은 단가표에 같은 거래처코드와 상품코드가 여러 행으로 존재하기 때문입니다. VLOOKUP은 기본적으로 위에서부터 찾다가 처음 만난 값을 가져옵니다. 그래서 2026-08-01부터 바뀐 단가가 있어도, 표 위쪽에 2026-07-01 단가가 먼저 있으면 예전 단가가 들어올 수 있습니다.

예를 들어 주문내역 시트는 아래처럼 구성되어 있다고 보겠습니다. 데이터는 4행부터 2000행까지 입력되어 있고, F열 적용단가와 G열 금액을 자동 계산하려는 상황입니다.

단가이력표는 같은 시트의 J열부터 M열에 있다고 가정하겠습니다. J4:M500 범위에 거래처별, 상품별, 적용시작일별 단가가 쌓입니다.

Q. 주문일보다 이전인 단가 중 가장 최신 단가를 어떻게 찾나요?

핵심 조건은 세 가지입니다. 거래처코드가 같아야 하고, 상품코드가 같아야 하며, 단가 적용시작일이 주문일보다 빠르거나 같아야 합니다. 그리고 그중 가장 최근 적용시작일의 단가를 가져와야 합니다.

Microsoft 365 또는 XLOOKUP을 사용할 수 있는 버전이라면, 단가이력표를 적용시작일 오름차순으로 정렬한 뒤 F4 셀에 아래 수식을 넣습니다. 이후 F2000까지 복사하면 됩니다.

=IFERROR(XLOOKUP(1,($J$4:$J$500=B4)*($K$4:$K$500=D4)*($L$4:$L$500<=A4),$M$4:$M$500,"단가확인",0,-1),"단가확인")

이 수식의 마지막 -1은 아래쪽부터 찾으라는 의미입니다. 단가이력표가 날짜 오름차순으로 정렬되어 있다면, 조건에 맞는 행 중 가장 아래쪽 행이 최신 단가가 됩니다.

예를 들어 A4가 2026-08-04, B4가 00125, D4가 P-1007이면 2026-08-01 단가인 19,200원이 나와야 합니다. 반대로 A4가 2026-07-20이면 2026-07-01 단가인 18,500원이 나와야 정상입니다.

Q. XLOOKUP이 없는 버전에서는 INDEX MATCH로도 가능한가요?

가능합니다. Excel 2019 이하 버전이거나 회사 PC에서 XLOOKUP이 지원되지 않는다면 INDEX, MATCH, IF 조합으로 처리할 수 있습니다. 다만 이 수식은 조건을 여러 개 동시에 판단하므로, 일부 구버전에서는 입력 후 Ctrl + Shift + Enter로 확정해야 합니다.

F4 셀에 아래 수식을 입력하고 아래로 복사합니다.

=IFERROR(INDEX($M$4:$M$500,MATCH(1,($J$4:$J$500=B4)*($K$4:$K$500=D4)*($L$4:$L$500=MAX(IF(($J$4:$J$500=B4)*($K$4:$K$500=D4)*($L$4:$L$500<=A4),$L$4:$L$500))),0)),"단가확인")

수식이 길어 보이지만 구조는 단순합니다. 먼저 같은 거래처코드와 상품코드 중 주문일 이전의 적용시작일만 골라냅니다. 그중 가장 큰 날짜를 찾고, 그 날짜의 단가를 INDEX로 가져오는 방식입니다.

여기서 자기 파일에 맞게 바꿀 부분은 범위입니다. 주문 데이터가 5000행까지 있다면 F4 수식은 그대로 아래로 복사하면 되고, 단가이력표가 2000행까지라면 $J$4:$J$500, $K$4:$K$500, $L$4:$L$500, $M$4:$M$500을 각각 2000행까지 늘려 주세요.

Q. 수식은 맞는데 결과가 ‘단가확인’으로 나오는 이유는 뭔가요?

대부분은 코드 형식과 날짜 형식 문제입니다. 거래처코드 00125가 주문내역에서는 텍스트인데, 단가이력표에서는 숫자 125로 들어가 있으면 같은 값처럼 보여도 엑셀은 다르게 봅니다.

이럴 때는 보조열을 만들어 먼저 기준을 통일하는 편이 안전합니다. 예를 들어 주문내역 H열에 정리된 거래처코드를 만들고, 단가이력표 N열에도 정리된 거래처코드를 만들 수 있습니다.

주문내역 H4 셀에는 아래 수식을 넣습니다. 거래처코드를 무조건 5자리 텍스트로 맞추는 공식입니다.

=TEXT(B4,"00000")

단가이력표 N4 셀에도 같은 방식으로 넣고 N500까지 복사합니다.

=TEXT(J4,"00000")

그다음 F4의 XLOOKUP 수식에서 B4 대신 H4를, J열 대신 N열을 사용하면 앞자리 0 문제를 줄일 수 있습니다.

=IFERROR(XLOOKUP(1,($N$4:$N$500=H4)*($K$4:$K$500=D4)*($L$4:$L$500<=A4),$M$4:$M$500,"단가확인",0,-1),"단가확인")

날짜도 마찬가지입니다. A열 주문일이나 L열 적용시작일이 실제 날짜가 아니라 20260804 같은 8자리 텍스트로 들어왔다면 비교 조건이 제대로 작동하지 않습니다. 이런 경우 주문내역 I열에 변환일자를 만들어 쓰면 좋습니다.

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

단가이력표의 적용시작일이 20260801 같은 형태라면 O4 셀에 아래 수식을 넣어 실제 날짜로 변환합니다.

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

이후 수식에서는 A4와 L열 대신 변환된 I4와 O열을 기준으로 비교하면 됩니다. 날짜처럼 보여도 왼쪽 정렬이면 텍스트일 가능성이 높으니 꼭 한 번 확인해 보세요.

Q. 금액까지 자동 계산하려면 어떤 수식을 넣나요?

단가가 F열에 들어왔다면 G4에는 금액 수식을 넣습니다. 실무에서는 단가가 없을 때 곱셈 오류가 나지 않도록 IF와 IFERROR를 같이 써 주는 편이 좋습니다.

=IF(F4="단가확인","확인필요",ROUND(E4*F4,0))

소수점 단가나 환산 수량이 있는 업종이라면 ROUND 자릿수를 조정하면 됩니다. 원 단위 반올림이면 0, 소수 둘째 자리까지 관리해야 하면 2로 바꾸면 됩니다.

또 하나 자주 놓치는 부분은 월말 기준 단가입니다. “8월 매출”이라고 해서 무조건 8월 31일 단가를 쓰는 것이 아니라, 각 주문일에 실제로 적용되던 단가를 써야 합니다. 그래서 이 방식은 월별 평균 단가보다 주문번호별 정산 검증에 더 잘 맞습니다.

Q. 단가가 제대로 들어갔는지 어떻게 검증하나요?

검증용으로 COUNTIFS를 먼저 써 보면 원인을 빨리 찾을 수 있습니다. 예를 들어 주문내역의 K4 셀에 해당 주문과 매칭되는 단가 후보가 몇 개인지 확인해 보겠습니다.

=COUNTIFS($N$4:$N$500,H4,$K$4:$K$500,D4,$O$4:$O$500,"<="&I4)

결과가 0이면 단가표에 해당 거래처·상품·주문일 이전 단가가 없다는 뜻입니다. 결과가 1 이상이면 후보는 존재하므로, 수식 범위나 정렬 상태를 확인하면 됩니다.

월별 금액 검산은 SUMIFS로 합니다. 예를 들어 P2에 기준월 첫날인 2026-08-01을 입력하고, Q2에 해당 월 매출금액 합계를 표시하려면 아래 수식을 사용합니다.

=SUMIFS($G$4:$G$2000,$A$4:$A$2000,">="&$P$2,$A$4:$A$2000,"<="&EOMONTH($P$2,0))

거래처별로도 보고 싶다면 R2에 거래처코드 00125를 입력하고, S2에 아래 수식을 넣습니다.

=SUMIFS($G$4:$G$2000,$A$4:$A$2000,">="&$P$2,$A$4:$A$2000,"<="&EOMONTH($P$2,0),$B$4:$B$2000,$R$2)

이때 B열 코드가 숫자와 텍스트로 섞여 있으면 SUMIFS 결과도 틀어질 수 있습니다. 앞에서 만든 H열 정리코드를 기준으로 검산하는 것이 더 안전합니다.

=SUMIFS($G$4:$G$2000,$A$4:$A$2000,">="&$P$2,$A$4:$A$2000,"<="&EOMONTH($P$2,0),$H$4:$H$2000,$R$2)

Q. 단가이력표 정렬은 꼭 해야 하나요?

XLOOKUP의 마지막 검색 옵션을 -1로 쓰는 방식은 정렬이 중요합니다. 같은 거래처와 상품 안에서 적용시작일이 오름차순으로 정리되어 있어야 최신 단가를 제대로 가져옵니다.

정렬 기준은 보통 J열 거래처코드, K열 상품코드, L열 적용시작일 순서가 좋습니다. 거래처와 상품별로 묶인 상태에서 날짜가 오래된 것부터 최신 순으로 내려오면 수식 해석도 쉬워지고 오류 추적도 편합니다.

INDEX MATCH 수식은 내부에서 가장 큰 날짜를 찾기 때문에 정렬 의존도가 낮지만, 그래도 단가표 관리 측면에서는 정렬해 두는 편이 좋습니다. 나중에 누가 봐도 “이 단가가 언제부터 바뀌었는지” 흐름이 보이기 때문입니다.

Q. 여러 월별 시트에 같은 수식을 매번 넣어야 하면 더 빠른 방법이 있나요?

월별로 2026년08월, 2026년09월처럼 시트가 나뉘어 있고, 각 시트의 A:G 구조가 동일하다면 VBA로 F열과 G열 수식을 한 번에 넣을 수 있습니다. 반복 작업이 많을 때는 수식을 복사하다가 범위를 잘못 잡는 실수가 더 위험합니다.

아래 코드는 현재 통합문서의 모든 시트를 돌면서, A4:A2000에 주문일이 있는 시트에만 F열 단가 수식과 G열 금액 수식을 입력합니다. 단가이력표는 단가이력이라는 시트의 A:D열에 있다고 가정합니다. 단가이력 시트의 A열은 거래처코드, B열은 상품코드, C열은 적용시작일, D열은 단가입니다.

Sub 주문일기준_최신단가_수식입력()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim formulaPrice As String
    Dim formulaAmount As String

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "단가이력" Then
            lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

            If lastRow >= 4 Then
                formulaPrice = "=IFERROR(XLOOKUP(1,(단가이력!$A$4:$A$500=B4)*(단가이력!$B$4:$B$500=D4)*(단가이력!$C$4:$C$500<=A4),단가이력!$D$4:$D$500,""단가확인"",0,-1),""단가확인"")"
                formulaAmount = "=IF(F4=""단가확인"",""확인필요"",ROUND(E4*F4,0))"

                ws.Range("F4:F" & lastRow).Formula2 = formulaPrice
                ws.Range("G4:G" & lastRow).Formula2 = formulaAmount
            End If
        End If
    Next ws

    MsgBox "단가와 금액 수식 입력이 끝났습니다.", vbInformation
End Sub

실행 전에는 반드시 파일을 복사해 테스트하세요. 매크로 실행 후에는 일반적인 Ctrl+Z 되돌리기가 되지 않는 경우가 많습니다. 원래 상태로 돌아가야 할 수 있으니, 실행 전 파일명 뒤에 “백업”을 붙여 하나 더 저장해 두는 것이 안전합니다.

또한 위 코드는 단가이력표의 날짜가 실제 날짜 형식이고, 월별 주문 시트의 B열 거래처코드와 D열 상품코드 형식이 단가이력표와 같다는 전제입니다. 앞자리 0이나 날짜 텍스트 문제가 섞여 있다면 먼저 보조열로 정리한 뒤 적용하는 편이 좋습니다.

Q. 실무에서 가장 많이 하는 실수는 무엇인가요?

첫째, 단가이력표에 같은 적용시작일이 중복으로 들어가는 경우입니다. 거래처코드 00125, 상품코드 P-1007, 적용시작일 2026-08-01이 두 줄이면 어떤 단가가 맞는지 판단하기 어렵습니다. 이때는 COUNTIFS로 중복을 먼저 잡아야 합니다.

=COUNTIFS($J$4:$J$500,J4,$K$4:$K$500,K4,$L$4:$L$500,L4)

결과가 2 이상이면 같은 조건의 단가가 중복 등록된 것입니다. 이 상태에서 수식을 아무리 고쳐도 정산 금액은 계속 흔들릴 수 있습니다.

둘째, 주문일보다 늦은 단가를 가져오는 실수입니다. 8월 4일 주문에 8월 10일 변경 단가가 들어가면 안 됩니다. 그래서 조건에 반드시 적용시작일 <= 주문일이 들어가야 합니다.

셋째, 거래처명으로 단가를 찾는 실수입니다. 거래처명은 띄어쓰기, 지점명, 괄호 표기가 조금씩 달라질 수 있습니다. 단가 정산은 거래처명보다 거래처코드로 맞추는 것이 훨씬 안정적입니다.

이럴 때 이렇게 쓰면 된다

단가표에 상품코드가 한 번만 나온다면 VLOOKUP으로도 충분합니다. 하지만 거래처별 단가가 다르고, 같은 상품의 단가가 날짜별로 바뀐다면 주문일 기준 최신 단가 찾기 방식으로 바꿔야 합니다.

XLOOKUP을 쓸 수 있고 단가이력표를 날짜순으로 관리할 수 있다면 XLOOKUP 마지막 일치 방식이 가장 간단합니다. 구버전이거나 정렬 상태가 불안하다면 INDEX MATCH로 주문일 이전의 가장 최신 적용시작일을 찾는 방식이 안정적입니다.

정산 파일에서는 수식 입력보다 검증이 더 중요합니다. COUNTIFS로 단가 후보가 있는지 확인하고, SUMIFS로 월별·거래처별 금액 합계를 대조하면 “왜 ERP와 다르지?”라는 시간을 꽤 줄일 수 있습니다.