엑셀 VLOOKUP 첫값만 가져올 때: 청구번호별 최신 입금일·미수잔액 정확히 맞추는 법
엑셀에서 청구현황 파일을 만들다 보면 VLOOKUP이 첫값만 가져와서 최신 입금일이 틀리는 문제가 자주 생깁니다. 특히 한 청구번호에 입금이 여러 번 나뉘어 들어오면, 화면상으로는 입금이 완료된 것 같은데 미수잔액은 남아 있거나, 반대로 최근 입금일이 예전 날짜로 표시됩니다.
이번 예제는 회계팀이나 영업관리팀에서 많이 쓰는 청구대장 기준으로 잡아보겠습니다. 단순히 VLOOKUP 하나로 끝내지 않고, 청구번호 정리, 날짜 정리, SUMIFS 합계, INDEX MATCH 최신값 찾기, COUNTIFS 검증까지 이어서 실무 파일에 바로 붙일 수 있는 흐름으로 설명하겠습니다.
문제 상황: 같은 청구번호가 여러 번 입금되면 VLOOKUP이 예전 입금일을 가져온다
시트는 총 3개라고 가정하겠습니다. 청구현황 시트에는 청구 기준 데이터가 있고, 입금내역 시트에는 은행 또는 ERP에서 내려받은 입금 데이터가 있습니다. 거래처마스터 시트에는 거래처코드와 거래처명이 정리되어 있습니다.
청구현황 시트 구조는 아래처럼 잡습니다. 데이터는 5행부터 1000행까지 있다고 보겠습니다.
| 열 | 열 이름 | 예시 | 설명 |
|---|---|---|---|
| A | 청구일 | 2026-07-03 | 세금계산서 또는 청구 기준일 |
| B | 거래처코드 | C001 | 거래처마스터와 연결 |
| C | 거래처명 | 한빛상사 | 수식으로 자동 입력 |
| D | 청구번호 | 127 | 실제로는 000127이어야 함 |
| E | 청구금액 | 1,200,000 | 청구한 금액 |
| F | 최근입금일 | 이번 글의 핵심 결과 | |
| G | 입금합계 | SUMIFS로 계산 | |
| H | 미수잔액 | 청구금액 - 입금합계 | |
| I | 상태 | 완납, 부분입금, 미입금 | |
| J | 관리청구번호 | 앞자리 0 보정용 |
입금내역 시트는 4행부터 3000행까지 데이터가 있다고 가정합니다.
| 열 | 열 이름 | 예시 | 설명 |
|---|---|---|---|
| A | 입금일 | 2026.07.05 | 텍스트 날짜로 들어오는 경우가 많음 |
| B | 거래처코드 | C001 | 입금 거래처 |
| C | 거래처명 | 한빛상사 | 은행 적요 기준일 수도 있음 |
| D | 청구번호 | 000127 | 청구현황 D열과 형식이 다를 수 있음 |
| E | 입금액 | 700,000 | 부분입금 포함 |
| F | 입금구분 | 계좌입금 | 구분값 |
| G | 메모 | 1차 입금 | 참고용 |
| H | 관리청구번호 | 앞자리 0 보정 | |
| I | 정리입금일 | 날짜 계산용 | |
| K | 이후입금건수 | 최신 입금일 판별 |
흔한 오류는 청구현황 F5에 아래처럼 VLOOKUP을 넣는 경우입니다.
=VLOOKUP(D5,입금내역!$D$4:$E$3000,2,0)이 수식은 청구번호가 처음 발견된 행의 값만 가져옵니다. 같은 청구번호 000127에 7월 5일 700,000원, 7월 18일 500,000원이 들어와도 VLOOKUP은 대체로 위쪽에 있는 7월 5일만 보고 멈춥니다. 그래서 최신 입금일, 입금상태, 미수잔액이 줄줄이 어긋납니다.
원인: 청구번호 형식, 날짜 형식, 부분입금 구조가 동시에 섞여 있다
이 문제는 수식 하나가 틀렸다기보다 데이터 모양이 서로 다르기 때문에 생깁니다. 청구현황 D열에는 127처럼 숫자로 보이고, 입금내역 D열에는 000127처럼 문자로 들어오는 식입니다. 사람 눈에는 같은 청구번호지만 엑셀에서는 다른 값으로 판단할 수 있습니다.
날짜도 마찬가지입니다. 입금내역 A열이 실제 날짜가 아니라 2026.07.05 같은 텍스트이면, 최신일 비교나 월별 집계에서 예상과 다른 결과가 나옵니다. 여기에 한 청구번호에 여러 입금 행이 붙으면 VLOOKUP 첫값 문제까지 겹칩니다.
따라서 해결 순서는 단순합니다. 먼저 비교할 키를 같은 모양으로 만들고, 날짜를 계산 가능한 값으로 바꾼 뒤, 입금액은 SUMIFS로 모두 더하고, 최근입금일은 INDEX MATCH와 COUNTIFS로 찾아오면 됩니다.
청구번호 앞자리 0부터 맞추면 SUMIFS 0 나옴을 많이 줄일 수 있다
청구현황 시트 J5에 관리청구번호를 만듭니다. 청구번호가 항상 6자리라고 가정하면, D5가 127이든 000127이든 결과를 000127로 맞출 수 있습니다.
=RIGHT("000000"&D5,6)이 수식을 J5에 입력하고 J1000까지 내려 채웁니다. 파일마다 청구번호 자릿수가 다르면 앞의 000000 개수와 마지막 6을 바꾸면 됩니다. 예를 들어 8자리 청구번호라면 00000000과 8로 바꾸면 됩니다.
입금내역 시트 H4에도 같은 방식으로 관리청구번호를 만듭니다.
=RIGHT("000000"&D4,6)이제 청구현황 J열과 입금내역 H열은 같은 기준으로 비교됩니다. SUMIFS가 0으로 나올 때 가장 먼저 확인해야 하는 부분이 바로 이 비교 키입니다.
입금일이 텍스트 날짜라면 정리입금일을 따로 만든다
입금내역 A열의 입금일이 2026.07.05처럼 점이 들어간 텍스트로 내려오는 경우가 있습니다. 이 상태에서는 날짜처럼 보이지만 실제 계산에서 제대로 비교되지 않을 수 있습니다. 입금내역 I4에 정리입금일을 만듭니다.
=IFERROR(DATE(LEFT(A4,4),MID(A4,6,2),RIGHT(A4,2)),A4)위 수식은 A4의 앞 4자리를 연도, 중간 2자리를 월, 오른쪽 2자리를 일로 보고 날짜를 다시 만드는 방식입니다. A열이 이미 정상 날짜라면 결과가 그대로 날짜로 보일 수 있고, 표시가 숫자로 나오면 셀 서식을 날짜로 바꾸면 됩니다.
만약 원본 날짜가 20260705처럼 들어온다면 MID 위치만 바꾸면 됩니다.
=IFERROR(DATE(LEFT(A4,4),MID(A4,5,2),RIGHT(A4,2)),A4)날짜 정리는 귀찮아 보여도 여기서 한번 잡아두면 월별 집계, 최신일 확인, 기간별 조건 합계가 훨씬 안정적으로 돌아갑니다.
입금합계는 VLOOKUP이 아니라 SUMIFS로 계산한다
부분입금이 있는 파일에서는 입금액을 VLOOKUP으로 가져오면 안 됩니다. 한 청구번호에 여러 행이 있을 수 있으므로, 입금액은 반드시 SUMIFS로 합산하는 편이 안전합니다.
청구현황 G5에 아래 수식을 입력합니다.
=IFERROR(SUMIFS(입금내역!$E$4:$E$3000,입금내역!$H$4:$H$3000,$J5),0)이 수식은 입금내역 H열의 관리청구번호가 청구현황 J5와 같은 행만 골라서 E열 입금액을 모두 더합니다. 결과가 0으로 나온다면 먼저 청구현황 J5와 입금내역 H열의 값이 정말 같은지 확인해야 합니다. 눈으로 보기에는 같아도 한쪽에 공백이 있거나 자릿수가 다르면 합계가 나오지 않습니다.
미수잔액은 H5에 계산합니다.
=ROUND(E5-G5,0)금액 계산에서 소수점이 숨어 있으면 0처럼 보이는데 실제로는 0.000001이 남는 경우가 있습니다. 그래서 실무 정산표에서는 ROUND로 한 번 정리해 두면 상태값 판단이 덜 흔들립니다.
상태는 I5에 넣습니다.
=IF(H5=0,"완납",IF(G5=0,"미입금","부분입금"))이제 입금합계와 미수잔액은 안정적으로 계산됩니다. 남은 문제는 최근입금일입니다.
최신 입금일은 COUNTIFS로 이후 입금이 있는지 세고 INDEX MATCH로 가져온다
최신 입금일을 찾을 때 XLOOKUP의 뒤에서 찾기 기능을 쓰면 간단할 때도 있습니다. 다만 입금내역이 날짜순으로 정렬되어 있지 않은 파일이라면 마지막 행이 최신일이라는 보장이 없습니다. 그래서 여기서는 COUNTIFS로 같은 청구번호 중 나보다 늦은 입금일이 있는지 먼저 세겠습니다.
입금내역 K4에 아래 수식을 입력하고 K3000까지 내려 채웁니다.
=COUNTIFS($H$4:$H$3000,H4,$I$4:$I$3000,">"&I4)이 수식의 의미는 간단합니다. 같은 관리청구번호 중에서 내 입금일보다 더 늦은 입금일이 몇 건 있는지 세는 것입니다. 결과가 0이면 그 행은 해당 청구번호의 최신 입금일 후보입니다.
이제 청구현황 F5에 최근입금일을 가져옵니다.
=IFERROR(INDEX(입금내역!$I$4:$I$3000,MATCH(1,(입금내역!$H$4:$H$3000=$J5)*(입금내역!$K$4:$K$3000=0),0)),"")아래 수식은 Microsoft 365 최신 버전에서는 일반 입력으로 사용할 수 있습니다. Excel 2021/2024 등 영구 버전에서는 사용 중인 버전에 따라 입력 방식이 다를 수 있고, 구버전에서는 Ctrl + Shift + Enter로 배열 수식 입력이 필요할 수 있습니다.
수식 구조를 풀어보면, 입금내역 H열이 청구현황 J5와 같고 K열이 0인 행을 찾은 뒤, 그 행의 I열 정리입금일을 가져오는 방식입니다. VLOOKUP처럼 첫 번째 값에서 멈추는 것이 아니라, 우리가 미리 표시해 둔 최신 입금 행을 찾아오는 흐름입니다.
거래처명은 코드 기준으로 XLOOKUP 또는 VLOOKUP으로 자동 입력한다
청구현황 C열 거래처명도 손으로 입력하면 나중에 거래처명이 조금씩 달라져 집계가 틀어질 수 있습니다. 거래처마스터 시트 A열에는 거래처코드, B열에는 거래처명이 있고, 데이터는 2행부터 500행까지 있다고 가정하겠습니다.
청구현황 C5에는 아래 수식을 넣습니다.
=IFERROR(XLOOKUP(B5,거래처마스터!$A$2:$A$500,거래처마스터!$B$2:$B$500),IFERROR(VLOOKUP(B5,거래처마스터!$A$2:$B$500,2,0),"코드확인"))XLOOKUP을 쓸 수 있는 버전이면 먼저 코드 기준으로 거래처명을 가져오고, 지원되지 않는 환경에서는 VLOOKUP 방식으로도 같은 구조를 만들 수 있습니다. 결과가 코드확인으로 나오면 거래처마스터에 코드가 없거나, 코드 앞뒤에 공백이 있는지 확인하면 됩니다.
검증 방법: 최신입금일이 맞는지 COUNTIFS로 다시 확인한다
수식을 넣고 끝내면 실무에서는 불안합니다. 특히 미수금 파일은 숫자 하나가 틀리면 바로 문의가 오기 때문에 검증용 칸을 몇 개 만들어 두는 것이 좋습니다.
예를 들어 청구현황 K열을 검증열로 쓰고, K5에 아래 수식을 넣습니다.
=COUNTIFS(입금내역!$H$4:$H$3000,$J5,입금내역!$I$4:$I$3000,">"&F5)이 결과가 0이면 F5에 표시된 최근입금일보다 더 늦은 입금일이 없다는 뜻입니다. 1 이상이면 최근입금일 수식이나 입금내역 날짜 정리에 문제가 있을 가능성이 큽니다.
또 하나 확인할 것은 입금합계입니다. 청구현황 G5의 SUMIFS 결과와 입금내역에서 같은 관리청구번호로 필터링한 E열 합계가 같은지 한두 건만 직접 비교해 보세요. 전체를 다 볼 필요는 없지만, 금액이 큰 거래처와 부분입금 거래처는 반드시 샘플 검산을 해보는 편이 좋습니다.
최신 입금일이 같은 날짜에 두 건 이상 있을 수도 있습니다. 예를 들어 같은 날 오전과 오후에 나뉘어 입금되면 K열이 0인 행이 여러 개 나올 수 있습니다. 이때 날짜만 필요하면 문제없지만, 메모나 입금구분까지 가져와야 한다면 같은 날짜 중 어떤 행을 기준으로 할지 업무 기준을 정해야 합니다.
기준월별 청구·입금 현황까지 응용하기
청구현황이 정리되면 월별 요약표도 쉽게 만들 수 있습니다. 예를 들어 청구현황 시트 L2에는 기준월을 2026-07-01 형태로 입력하고, M5에는 거래처코드 C001을 입력한다고 하겠습니다.
N5에는 해당 거래처의 기준월 청구건수를 계산합니다.
=COUNTIFS($B$5:$B$1000,$M5,$A$5:$A$1000,">="&$L$2,$A$5:$A$1000,"<="&EOMONTH($L$2,0))O5에는 기준월 청구금액 합계를 계산합니다.
=SUMIFS($E$5:$E$1000,$B$5:$B$1000,$M5,$A$5:$A$1000,">="&$L$2,$A$5:$A$1000,"<="&EOMONTH($L$2,0))P5에는 기준월 청구분의 입금합계를 계산합니다.
=SUMIFS($G$5:$G$1000,$B$5:$B$1000,$M5,$A$5:$A$1000,">="&$L$2,$A$5:$A$1000,"<="&EOMONTH($L$2,0))여기서 중요한 점은 기준월 셀 L2를 반드시 실제 날짜로 입력해야 한다는 것입니다. 2026-07 같은 텍스트로 입력하면 EOMONTH가 원하는 대로 작동하지 않을 수 있습니다. 표시만 2026-07로 보이게 하고 싶다면 셀 서식을 yyyy-mm으로 바꾸면 됩니다.
반복해서 들어오는 청구번호 앞자리 0 정리는 VBA로 한 번에 처리할 수 있다
매번 보조열 수식을 쓰는 것도 좋지만, 외부에서 받은 파일의 청구번호 자체를 6자리 텍스트로 통일해야 하는 경우도 있습니다. 이럴 때는 선택한 범위만 일괄 변환하는 간단한 VBA가 편합니다.
실행 전에는 반드시 원본 파일을 복사해 두세요. 아래 코드는 현재 선택한 셀의 값을 6자리 텍스트로 바꿉니다. 숫자 127은 000127로 바뀌며, 이미 000127인 값은 그대로 유지됩니다. 되돌리려면 실행 직후 Ctrl + Z가 안 될 수 있으니, 백업 파일을 열어 복구하는 방식이 안전합니다.
Sub 선택영역_청구번호6자리텍스트()
Dim c As Range
For Each c In Selection
If Len(c.Value) > 0 Then
c.NumberFormat = "@"
c.Value = Right("000000" & CStr(c.Value), 6)
End If
Next c
End Sub적용 방법은 간단합니다. 청구번호가 들어 있는 범위, 예를 들어 청구현황 D5:D1000 또는 입금내역 D4:D3000을 선택한 뒤 이 매크로를 실행하면 됩니다. 단, 청구번호가 6자리를 초과하는 파일에는 그대로 쓰면 뒤 6자리만 남으므로, 자릿수 규칙을 먼저 확인해야 합니다.
실무에서 적용할 때 볼 것
이 문제는 VLOOKUP이 나쁘다기보다, 같은 청구번호에 여러 행이 생기는 업무 구조를 VLOOKUP 하나로 처리하려 해서 생깁니다. 입금합계는 SUMIFS, 최신 여부는 COUNTIFS, 최신 입금일은 INDEX MATCH로 나누어 처리하면 수식은 조금 길어져도 결과는 훨씬 안정적입니다.
파일을 고칠 때는 청구번호 형식부터 맞추고, 날짜가 진짜 날짜인지 확인한 다음, 금액 합계와 최신일을 따로 검증해 보세요. SUMIFS가 0으로 나오거나 VLOOKUP이 첫값만 가져오는 증상은 대부분 이 순서대로 보면 원인을 빨리 찾을 수 있습니다.