쿠폰 정산 VLOOKUP #N/A가 계속 뜰 때: 승인번호 앞자리 0·월 중복까지 잡는 검산 수식

쿠폰 정산표를 맞추다 보면 VLOOKUP #N/A가 계속 뜨거나, 분명 같은 승인번호인데 결제원장 금액을 못 가져오는 일이 자주 생깁니다. 특히 승인번호가 000812처럼 앞자리 0을 포함하거나, 원장에는 20260701-000812처럼 날짜가 붙어 들어오는 파일이면 단순 조회로는 거의 한 번씩 틀어집니다.

오늘 예제는 쿠폰사에서 받은 쿠폰정산 시트와 PG사에서 받은 결제원장 시트를 맞추는 상황입니다. 한 건씩 눈으로 찾지 않고, 승인번호를 정리한 뒤 XLOOKUP, VLOOKUP, INDEX MATCH, COUNTIFS, SUMIFS로 누락·중복·금액차이를 끝까지 검산해 보겠습니다.

실무 상황: 승인번호는 같은데 VLOOKUP 안됨, 월마감 쿠폰 금액이 안 맞는 경우

먼저 파일 구조를 구체적으로 잡고 가겠습니다. 결제원장 시트는 5행부터 데이터가 있고, A열부터 J열까지 사용합니다. 쿠폰정산 시트도 5행부터 데이터가 있으며, A열부터 J열까지 검산용 열을 추가해 사용할 예정입니다.

예를 들어 쿠폰정산!M2에는 2026-07, N2에는 해피쿠폰을 입력해 둡니다. 이 조건으로 7월 해피쿠폰 차감액 합계, 원장 매칭 여부, 금액 차이를 같이 확인하는 흐름입니다.

예제 데이터: 앞자리 0, 날짜 붙은 승인번호, 월 중복이 섞인 상태

아래처럼 결제원장 승인번호는 형식이 제각각입니다. 어떤 행은 날짜와 하이픈이 붙어 있고, 어떤 행은 숫자로 들어와 앞자리 0이 사라져 있습니다.

쿠폰정산 시트는 쿠폰사에서 내려받은 파일이라 승인번호가 짧게 들어옵니다. 이 상태에서 C열 승인번호만 가지고 VLOOKUP을 걸면 000812를 못 찾거나, 다른 달의 000812와 섞일 수 있습니다.

원인: 승인번호만 맞추면 안 되고, 월까지 붙인 대사키가 필요합니다

이 문제의 핵심은 승인번호가 고유값이 아니라는 점입니다. 000812라는 번호는 7월에도 있고 8월에도 다시 나올 수 있습니다. 그래서 승인번호만으로 조회하면 조회가 되더라도 틀린 달의 금액을 가져올 위험이 있습니다.

또 하나는 앞자리 0입니다. 엑셀이 승인번호를 숫자로 인식하면 000815가 815로 바뀝니다. 사람 눈에는 같은 번호로 보여도, 수식 입장에서는 텍스트 000815와 숫자 815가 다르게 취급될 수 있습니다.

그래서 이번 예제에서는 연월 + 승인번호 끝 6자리를 합쳐서 대사키를 만듭니다. 예를 들어 2026년 7월 승인번호 000812는 202607-000812가 됩니다.

결제원장 보조열 만들기: I열 승인키, J열 순매출

먼저 결제원장 시트의 I열과 J열을 비워 둡니다. I4에는 승인키, J4에는 순매출이라고 적습니다.

결제원장!I5에 아래 수식을 입력하고 I3000행까지 복사합니다. A열 결제일에서 연월을 만들고, C열 승인번호의 오른쪽 6자리를 가져와 앞자리 0을 보정합니다.

=TEXT(A5,"yyyymm")&"-"&RIGHT("000000"&C5,6)

이 수식은 C5가 20260701-000812여도 오른쪽 6자리인 000812를 가져옵니다. C5가 숫자 815로 들어와 있어도 앞에 0을 붙여 000815로 맞춥니다.

다음으로 결제원장!J5에는 순매출을 계산합니다. 결제금액 F열에서 환불금액 G열을 뺀 뒤, 원 단위 반올림을 적용합니다.

=ROUND(F5-G5,0)

금액에 소수점이 없으면 ROUND가 꼭 필요하지는 않습니다. 다만 PG 수수료나 해외 결제 자료가 섞이는 파일에서는 소수점 잔차 때문에 1원 차이가 생길 수 있어, 검산 열에는 ROUND를 습관적으로 넣어 두는 편이 안전합니다.

쿠폰정산 시트에도 같은 승인키 만들기

이제 쿠폰정산 시트에서 F열부터 검산 열을 만들겠습니다. F4에는 승인키, G4에는 원장순매출, H4에는 원장수수료, I4에는 원장중복건수, J4에는 검증결과를 적습니다.

쿠폰정산!F5에는 아래 수식을 입력합니다. 사용일 A열의 연월과 쿠폰승인번호 C열의 6자리를 합쳐, 결제원장과 같은 기준의 키를 만듭니다.

=TEXT(A5,"yyyymm")&"-"&RIGHT("000000"&C5,6)

여기서 중요한 점은 양쪽 시트의 키 생성 방식이 완전히 같아야 한다는 것입니다. 한쪽은 날짜를 yyyy-mm으로 만들고, 다른 한쪽은 yyyymm으로 만들면 사람 눈에는 비슷해 보여도 매칭은 실패합니다.

XLOOKUP으로 원장 금액 가져오기, 없으면 빈칸 처리

이제 쿠폰정산!G5에서 승인키 F5를 기준으로 결제원장의 순매출을 가져옵니다. Microsoft 365 또는 Excel 2021 이후 버전이라면 XLOOKUP을 쓰는 방식이 가장 읽기 쉽습니다.

=IFERROR(XLOOKUP(F5,결제원장!$I$5:$I$3000,결제원장!$J$5:$J$3000),"")

이 수식에서 바꿔야 할 부분은 범위입니다. 결제원장 데이터가 3000행보다 많다면 $I$5:$I$3000과 $J$5:$J$3000의 끝 행을 실제 마지막 행에 맞춰 늘려 주세요.

수수료도 같이 가져와야 한다면 쿠폰정산!H5에 아래 수식을 넣습니다. 이 예제에서는 결제원장 H열에 정산수수료가 있다고 가정합니다.

=IFERROR(XLOOKUP(F5,결제원장!$I$5:$I$3000,결제원장!$H$5:$H$3000),"")

VLOOKUP을 꼭 써야 한다면, 조회키 열이 맨 앞에 오게 잡습니다

회사 공용 파일이나 예전 버전 엑셀 때문에 XLOOKUP을 쓰기 어려운 경우도 있습니다. 그럴 때는 VLOOKUP을 써도 됩니다. 다만 VLOOKUP은 찾을 값이 범위의 첫 번째 열에 있어야 하므로, 결제원장 I열 승인키를 기준으로 I:J 범위를 잡아야 합니다.

쿠폰정산!G5에 VLOOKUP 방식으로 작성하면 아래와 같습니다.

=IFERROR(VLOOKUP(F5,결제원장!$I$5:$J$3000,2,FALSE),"")

여기서 FALSE는 정확히 일치하는 값만 찾겠다는 뜻입니다. 정산 대사에서는 근사값 조회를 쓰면 안 됩니다. 승인번호는 비슷한 값이 아니라 정확히 같은 값이어야 합니다.

INDEX MATCH 방식으로도 같은 결과를 만들 수 있습니다

XLOOKUP이 없는 환경이지만 VLOOKUP의 열 위치 제한이 불편하다면 INDEX와 MATCH 조합도 좋습니다. 쿠폰정산!G5에 아래처럼 입력하면, F5 승인키가 결제원장 I열에서 몇 번째에 있는지 찾고 J열의 순매출을 반환합니다.

=IFERROR(INDEX(결제원장!$J$5:$J$3000,MATCH(F5,결제원장!$I$5:$I$3000,0)),"")

MATCH의 마지막 인수 0도 정확히 일치 조건입니다. 정산표에서 이 부분을 1 또는 생략으로 두면 정렬 상태에 따라 엉뚱한 행을 가져올 수 있으니 꼭 확인해야 합니다.

COUNTIFS로 중복 승인키부터 확인해야 조회 결과를 믿을 수 있습니다

조회 수식이 정상으로 보이더라도, 결제원장에 같은 승인키가 2건 이상 있으면 첫 번째 값만 가져와 버릴 수 있습니다. 그래서 쿠폰정산!I5에는 원장에 같은 승인키가 몇 건 있는지 먼저 세어 둡니다.

=COUNTIFS(결제원장!$I$5:$I$3000,F5)

I열 결과가 0이면 원장에 없는 건입니다. 1이면 정상적으로 한 건만 매칭된 상태입니다. 2 이상이면 같은 월, 같은 승인번호가 결제원장에 여러 줄 있다는 뜻이므로 주문번호나 금액을 추가로 확인해야 합니다.

검증결과 열에서 원장없음, 중복확인, 금액차이를 한 번에 표시하기

이제 쿠폰정산!J5에 상태값을 표시해 보겠습니다. 여기서는 쿠폰차감액 E열이 원장 순매출보다 클 수 없다는 기준으로 단순 검산합니다. 실제 회사 기준에 따라 비교 대상은 결제금액, 순매출, 쿠폰차감 전 금액 등으로 바꾸면 됩니다.

=IF(I5=0,"원장없음",IF(I5>1,"중복확인",IF(E5>G5,"금액확인","정상")))

조금 더 엄격하게 확인하려면 쿠폰차감액과 원장 금액의 차이를 별도 열에 만들 수도 있습니다. 예를 들어 K열을 사용한다면 K4에 차이라고 적고, K5에 아래 수식을 넣습니다.

=IFERROR(ROUND(G5-E5,0),"")

이 차이 금액이 음수라면 쿠폰차감액이 원장 순매출보다 큰 상태입니다. 양수라도 회사 정산 기준상 허용 범위를 벗어나면 별도로 표시하는 식으로 응용할 수 있습니다.

SUMIFS로 기준월·쿠폰사별 합계까지 맞춰 보기

건별 검산이 끝났다면 마지막으로 월별 합계를 확인합니다. 쿠폰정산!M2에는 기준월 2026-07, N2에는 쿠폰사 해피쿠폰을 입력합니다. 그리고 O2에 해당 월 쿠폰차감액 합계를 구합니다.

=SUMIFS($E$5:$E$800,$A$5:$A$800,">="&DATE(LEFT($M$2,4),RIGHT($M$2,2),1),$A$5:$A$800,"<"&EOMONTH(DATE(LEFT($M$2,4),RIGHT($M$2,2),1),0)+1,$B$5:$B$800,$N$2)

여기서 종료 조건을 < 다음 달 1일 방식으로 둔 이유가 있습니다. 사용일 A열에 날짜뿐 아니라 시간이 붙어 있는 경우, <= 월말 조건은 월말 오후 데이터가 빠질 수 있습니다. 그래서 월초 이상, 다음 달 1일 미만 조건이 더 안전합니다.

같은 방식으로 정상 건만 합계를 보고 싶다면 J열 검증결과 조건을 하나 더 추가합니다.

=SUMIFS($E$5:$E$800,$A$5:$A$800,">="&DATE(LEFT($M$2,4),RIGHT($M$2,2),1),$A$5:$A$800,"<"&EOMONTH(DATE(LEFT($M$2,4),RIGHT($M$2,2),1),0)+1,$B$5:$B$800,$N$2,$J$5:$J$800,"정상")

흔한 실수: 수식은 맞는데 결과가 계속 이상할 때 보는 순서

첫 번째로 날짜 형식을 확인합니다. A열 결제일이나 사용일이 진짜 날짜가 아니라 20260701 같은 숫자 또는 텍스트라면 TEXT, DATE, EOMONTH 조건이 예상대로 작동하지 않을 수 있습니다. 이 경우 날짜 변환 열을 따로 만들어야 합니다.

예를 들어 쿠폰정산 A열이 20260701 형태라면 별도 열에 아래처럼 날짜로 바꿀 수 있습니다.

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

두 번째는 승인번호 뒤쪽 공백입니다. 웹에서 내려받은 파일은 승인번호 뒤에 보이지 않는 공백이 붙어 있는 경우가 있습니다. 이때는 같은 수식처럼 보여도 RIGHT 결과가 달라질 수 있으니, 원본 C열을 복사해 값 붙여넣기 한 뒤 불필요한 공백을 정리하고 다시 계산해 보세요.

세 번째는 범위 행 수입니다. 쿠폰정산은 800행까지인데 결제원장은 5000행까지 있는 파일이라면, 조회 범위가 $I$5:$I$3000에서 끊겨 뒤쪽 원장 데이터가 누락됩니다. 월마감 때 갑자기 몇 건만 안 맞는다면 범위 끝 행부터 보는 것이 빠릅니다.

매월 반복한다면 VBA로 보조열 수식 채우기 자동화

매달 같은 이름의 시트에 같은 보조열을 만들고 수식을 아래로 채운다면 VBA로 줄일 수 있습니다. 아래 코드는 결제원장 시트의 I:J열, 쿠폰정산 시트의 F:J열에 제목과 수식을 자동 입력합니다.

실행 전에는 파일을 반드시 복사본으로 저장해 두세요. 매크로 실행 후에는 일반 실행 취소가 되지 않는 경우가 많습니다. 잘못 실행했다면 저장하지 않고 닫거나, 미리 만든 백업 파일로 되돌리는 방식이 안전합니다.

Sub 쿠폰정산_검산수식_채우기()
    Dim wsPay As Worksheet
    Dim wsCoupon As Worksheet
    Dim lastPay As Long
    Dim lastCoupon As Long

    Set wsPay = ThisWorkbook.Worksheets("결제원장")
    Set wsCoupon = ThisWorkbook.Worksheets("쿠폰정산")

    lastPay = wsPay.Cells(wsPay.Rows.Count, "A").End(xlUp).Row
    lastCoupon = wsCoupon.Cells(wsCoupon.Rows.Count, "A").End(xlUp).Row

    wsPay.Range("I4").Value = "승인키"
    wsPay.Range("J4").Value = "순매출"

    wsPay.Range("I5:I" & lastPay).Formula = "=TEXT(A5,""yyyymm"")&""-""&RIGHT(""000000""&C5,6)"
    wsPay.Range("J5:J" & lastPay).Formula = "=ROUND(F5-G5,0)"

    wsCoupon.Range("F4").Value = "승인키"
    wsCoupon.Range("G4").Value = "원장순매출"
    wsCoupon.Range("H4").Value = "원장수수료"
    wsCoupon.Range("I4").Value = "원장중복건수"
    wsCoupon.Range("J4").Value = "검증결과"

    wsCoupon.Range("F5:F" & lastCoupon).Formula = "=TEXT(A5,""yyyymm"")&""-""&RIGHT(""000000""&C5,6)"
    wsCoupon.Range("G5:G" & lastCoupon).Formula = "=IFERROR(XLOOKUP(F5,결제원장!$I$5:$I$" & lastPay & ",결제원장!$J$5:$J$" & lastPay & "),"""")"
    wsCoupon.Range("H5:H" & lastCoupon).Formula = "=IFERROR(XLOOKUP(F5,결제원장!$I$5:$I$" & lastPay & ",결제원장!$H$5:$H$" & lastPay & "),"""")"
    wsCoupon.Range("I5:I" & lastCoupon).Formula = "=COUNTIFS(결제원장!$I$5:$I$" & lastPay & ",F5)"
    wsCoupon.Range("J5:J" & lastCoupon).Formula = "=IF(I5=0,""원장없음"",IF(I5>1,""중복확인"",IF(E5>G5,""금액확인"",""정상"")))"

    MsgBox "쿠폰 정산 검산 수식 입력이 완료되었습니다.", vbInformation
End Sub

이 코드는 시트명이 정확히 결제원장, 쿠폰정산일 때만 동작합니다. 시트명이 다르면 코드의 해당 부분을 실제 시트명으로 바꿔야 합니다. 또한 결제금액, 환불금액, 수수료 열 위치가 예제와 다르면 F, G, H열 참조도 함께 수정해야 합니다.

이 방식이 편한 이유: 한 건 조회와 월별 합계를 같은 기준으로 맞춘다

정산 파일에서 제일 위험한 상태는 건별 조회 기준과 월별 합계 기준이 서로 다른 경우입니다. 건별로는 승인번호만 보고, 합계는 날짜와 쿠폰사로 보면 나중에 어디서 차이가 났는지 추적하기 어렵습니다.

이번처럼 연월+승인번호 6자리로 승인키를 만들면, 조회 실패와 월 중복 문제를 같이 줄일 수 있습니다. 여기에 COUNTIFS로 중복을 먼저 표시하고, SUMIFS로 월별 정상 건만 합산하면 보고서 숫자를 설명하기가 훨씬 쉬워집니다.

다음에는 같은 구조에 주문번호 D열까지 붙여 연월+승인번호+주문번호 대사키로 확장할 수 있습니다. 쿠폰, 포인트, 간편결제 정산처럼 승인번호가 반복되는 파일이라면 이 방식부터 적용해 보시면 좋습니다.