엑셀 VLOOKUP이 첫 번째 상담상태만 가져올 때: 고객별 최신값을 INDEX MATCH와 COUNTIFS로 잡는 법

고객 상담이력표에서 VLOOKUP이 첫 번째 값만 가져와서 최신 상담상태가 안 맞는 오류가 자주 납니다. 특히 고객ID 하나에 상담이 여러 번 쌓이는 구조라면, VLOOKUP은 가장 최근 행이 아니라 위에서 처음 만난 행을 가져오기 때문에 보고서 상태가 오래된 값으로 보일 수 있습니다.

이번 예제는 상담센터, 영업관리, CS 처리현황 파일에서 그대로 자주 보는 형태입니다. 단순히 “XLOOKUP 쓰세요”로 끝내지 않고, 원인이 어디에 있는지부터 COUNTIFS 보조열, INDEX MATCH, IFERROR 검산까지 순서대로 잡아보겠습니다.

Q. 고객ID로 VLOOKUP 했는데 왜 최신 상담상태가 아니라 예전 값이 나오나요?

예를 들어 상담이력 시트에 아래처럼 데이터가 있다고 보겠습니다. 실제 파일에서는 2행부터 5000행까지 계속 누적되는 원장 구조입니다.

요약표는 요약 시트에 만들겠습니다. A열에는 고객ID를 넣고, B열에는 고객명, C열에는 최신 상담일, D열에는 최신 처리상태, E열에는 최신 담당자, F열에는 기준월 상담횟수를 표시하는 상황입니다.

많이 쓰는 수식은 보통 이런 형태입니다.

=VLOOKUP(A2,상담이력!$B$2:$F$5000,4,FALSE)

이 수식은 고객ID가 같은 첫 번째 행을 찾습니다. 즉 상담이력 시트에서 C0007 고객이 5월, 6월, 7월에 각각 상담했다면, 가장 위에 있는 5월 상담상태를 가져올 수 있습니다. 그래서 “완료로 바꿨는데 요약표는 아직 처리중으로 보인다”는 문제가 생깁니다.

Q. 원장 정렬을 바꾸지 않고 최신 행만 표시할 수 있나요?

가능합니다. 핵심은 상담이력 시트에 보조열을 만들어 이 고객ID의 더 나중 상담이 아래나 위에 있는지를 먼저 판단하는 것입니다. 여기서는 H열, I열, J열을 보조열로 사용하겠습니다.

상담이력 시트의 H1에는 이후상담건수, I1에는 동일일자아래건수, J1에는 최신조회키라고 입력합니다. 데이터 범위는 A2:F5000이라고 가정합니다.

먼저 H2에 아래 수식을 넣고 H5000까지 복사합니다. 같은 고객ID 중 현재 행보다 상담일이 더 늦은 건수가 몇 개인지 세는 수식입니다.

=COUNTIFS($B$2:$B$5000,B2,$A$2:$A$5000,">"&A2)

결과가 0이면, 적어도 상담일 기준으로는 현재 행보다 더 늦은 상담이 없다는 뜻입니다. 다만 같은 날짜에 상담이 2번 이상 입력된 경우가 있습니다. 이때는 같은 날짜 안에서 아래쪽에 있는 행을 최신으로 보도록 I열에서 한 번 더 정리합니다.

I2에 아래 수식을 넣고 I5000까지 복사합니다.

=COUNTIFS($B2:$B$5000,B2,$A2:$A$5000,A2)-1

이 수식은 현재 행부터 마지막 행까지 내려가면서 같은 고객ID, 같은 상담일이 몇 개 더 있는지 확인합니다. 같은 날짜 상담이 여러 줄이면 위쪽 행은 1 이상, 가장 아래쪽 행은 0이 됩니다.

마지막으로 J2에는 최신 행에만 조회키가 생기도록 아래 수식을 넣습니다.

=IF(H2+I2=0,B2&"|최신","")

이제 고객별 최신 상담 행에는 C0007|최신 같은 키가 생기고, 최신이 아닌 행은 빈칸으로 남습니다. 원장을 날짜순으로 다시 정렬하지 않아도 요약표에서 정확히 최신 행만 찾을 수 있습니다.

Q. 요약 시트에서 최신 상담일, 처리상태, 담당자는 어떤 수식으로 가져오나요?

요약 시트 A2에 고객ID가 들어 있다고 보겠습니다. C2에는 최신 상담일을 가져옵니다. 상담이력 시트 J열에서 고객ID|최신 키를 찾고, 같은 행의 A열 상담일을 가져오는 방식입니다.

=IFERROR(INDEX(상담이력!$A$2:$A$5000,MATCH($A2&"|최신",상담이력!$J$2:$J$5000,0)),"상담없음")

D2에는 최신 처리상태를 가져옵니다. 가져올 범위만 E열로 바뀝니다.

=IFERROR(INDEX(상담이력!$E$2:$E$5000,MATCH($A2&"|최신",상담이력!$J$2:$J$5000,0)),"상담없음")

E2에는 최신 담당자를 가져옵니다.

=IFERROR(INDEX(상담이력!$F$2:$F$5000,MATCH($A2&"|최신",상담이력!$J$2:$J$5000,0)),"상담없음")

이 방식의 장점은 VLOOKUP처럼 “첫 번째로 만난 고객ID”에 끌려가지 않는다는 점입니다. J열에서 최신으로 표시된 행만 찾기 때문에, 같은 고객ID가 10번 나와도 최신 행 하나만 요약표로 가져옵니다.

Q. XLOOKUP을 쓸 수 있으면 수식이 더 짧아지나요?

Microsoft 365 또는 Excel 2021 이후 버전이라면 XLOOKUP으로 조금 더 읽기 쉽게 작성할 수 있습니다. 아래 수식은 같은 원리로 J열의 최신조회키를 찾고 E열의 처리상태를 반환합니다.

=XLOOKUP($A2&"|최신",상담이력!$J$2:$J$5000,상담이력!$E$2:$E$5000,"상담없음",0)

다만 여기서도 중요한 것은 XLOOKUP 자체가 아니라 최신 행을 구분하는 기준입니다. 원장이 날짜순으로 매번 정확히 쌓인다는 보장이 없다면, 단순히 아래에서부터 찾는 방식보다 H열, I열, J열처럼 최신키를 만들어두는 편이 안전합니다.

Q. 이번 달 상담횟수도 같이 검산할 수 있나요?

요약 시트 H1에 기준월을 날짜로 입력합니다. 예를 들어 2026년 7월을 보고 싶다면 H1에 2026-07-01을 넣습니다. F2에는 해당 고객의 기준월 상담횟수를 계산합니다.

=COUNTIFS(상담이력!$B$2:$B$5000,$A2,상담이력!$A$2:$A$5000,">="&$H$1,상담이력!$A$2:$A$5000,"<"&EOMONTH($H$1,0)+1)

여기서 종료 조건을 <= EOMONTH이 아니라 < 다음 달 1일로 잡은 이유가 있습니다. 상담일에 시간이 숨어 있는 경우, 예를 들어 2026-07-31 14:30처럼 들어온 값은 단순히 2026-07-31 이하 조건에서 예상과 다르게 빠질 수 있습니다. 그래서 실무에서는 월 조건을 잡을 때 이 방식이 더 안전합니다.

Q. 상담일이 20260701처럼 텍스트로 들어오면 어떻게 하나요?

COUNTIFS 날짜 조건이 0으로 나온다면 상담일이 날짜가 아니라 텍스트일 가능성이 큽니다. 상담이력 시트 A열이 20260701 형태라면 G열에 정상 날짜 변환용 열을 하나 만들고, G2에 아래 수식을 넣습니다.

=IFERROR(DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)),A2)

그다음 앞에서 사용한 수식의 상담일 범위를 A열 대신 G열로 바꾸면 됩니다. 예를 들어 H2의 이후상담건수 수식은 이렇게 바뀝니다.

=COUNTIFS($B$2:$B$5000,B2,$G$2:$G$5000,">"&G2)

월 상담횟수 수식도 상담일 범위를 G열로 바꿉니다. 날짜 변환이 제대로 됐는지 확인하려면 G열 셀 서식을 날짜로 바꿔보고, 오른쪽 정렬된 날짜값으로 보이는지 확인하면 됩니다.

Q. 고객명이 비어 있거나 고객ID 앞자리 0이 사라지는 문제도 같이 확인해야 하나요?

요약 시트 B2의 고객명은 고객마스터 시트에서 가져오면 관리가 편합니다. 고객마스터 시트는 A열 고객ID, B열 고객명, C열 등급으로 되어 있고 데이터 범위가 A2:C1000이라고 하겠습니다.

=IFERROR(VLOOKUP($A2,고객마스터!$A$2:$C$1000,2,FALSE),"고객ID 확인")

이때 고객ID가 숫자로 저장되어 앞자리 0이 사라지면 VLOOKUP, INDEX MATCH, COUNTIFS가 모두 빗나갈 수 있습니다. 예를 들어 요약 시트에는 000123인데 상담이력 시트에는 123으로 들어가 있으면 서로 다른 값입니다. 고객ID 열은 양쪽 모두 텍스트 형식으로 맞추고, 이미 깨진 데이터는 원본 다운로드 단계부터 다시 확인하는 편이 안전합니다.

Q. 보조열 수식이 너무 많을 때 한 번에 넣는 방법은 없나요?

상담이력 행이 매달 수천 줄씩 늘어난다면 H:J 보조열 수식을 매번 복사하는 것도 번거롭습니다. 이럴 때는 아래 VBA로 현재 통합문서의 상담이력 시트에 보조열 수식을 한 번에 채울 수 있습니다.

실행 전에는 파일을 먼저 다른 이름으로 저장해두세요. 매크로 실행 후에는 일반적인 Ctrl+Z 되돌리기가 되지 않습니다. 되돌리려면 저장하지 않고 닫거나, H:J열의 수식을 삭제한 뒤 백업본에서 다시 시작하는 방식이 안전합니다.

Sub 최신상담키_보조열채우기()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("상담이력")
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "상담이력 데이터가 없습니다."
        Exit Sub
    End If

    ws.Range("H1").Value = "이후상담건수"
    ws.Range("I1").Value = "동일일자아래건수"
    ws.Range("J1").Value = "최신조회키"

    ws.Range("H2:H" & lastRow).Formula = "=COUNTIFS($B$2:$B$" & lastRow & ",B2,$A$2:$A$" & lastRow & ","">""&A2)"
    ws.Range("I2:I" & lastRow).Formula = "=COUNTIFS($B2:$B$" & lastRow & ",B2,$A2:$A$" & lastRow & ",A2)-1"
    ws.Range("J2:J" & lastRow).Formula = "=IF(H2+I2=0,B2&""|최신"","""")"

    MsgBox "최신 상담 조회키 생성이 완료되었습니다."
End Sub

이 코드는 상담일이 A열, 고객ID가 B열에 있는 구조에서만 그대로 맞습니다. 만약 내 파일에서 상담일이 C열이고 고객ID가 D열이라면 코드 안의 A, B 열 기준을 반드시 바꿔야 합니다.

Q. 결과가 맞는지 빠르게 검증하는 순서는 무엇인가요?

먼저 상담이력 시트에서 특정 고객ID 하나를 필터로 걸어봅니다. 예를 들어 C0007만 필터링한 뒤 상담일이 가장 늦은 행의 J열에 C0007|최신이 찍혀 있는지 확인합니다.

그다음 요약 시트에서 같은 고객ID의 C열 최신 상담일과 D열 처리상태가 필터링한 최신 행과 같은지 봅니다. 마지막으로 F열 월 상담횟수는 상담이력 시트에서 고객ID와 기준월을 필터로 걸었을 때 보이는 행 수와 비교하면 됩니다.

금액 검산처럼 숫자가 필요한 상황이라면 ROUND를 같이 써서 표시 차이를 줄일 수 있지만, 이번 상담상태 조회처럼 텍스트를 가져오는 작업에서는 최신 행을 정확히 고르는 것이 가장 중요합니다. 날짜, 고객ID, 중복일자 이 세 가지만 맞으면 대부분의 오류는 잡힙니다.

이럴 때 이렇게 쓰면 된다

고객ID 하나에 이력이 여러 줄 쌓이는 파일에서 VLOOKUP 결과가 예전 값으로 보이면, 먼저 “첫 번째 일치값을 가져오는 구조”인지 의심하면 됩니다. 단순 단가표나 고객마스터처럼 키가 한 번만 나오는 표는 VLOOKUP으로 충분하지만, 상담이력·입출고이력·상태변경이력처럼 같은 키가 반복되는 원장은 최신키를 만든 뒤 INDEX MATCH나 XLOOKUP으로 가져오는 편이 안전합니다.

정리하면, 중복 없는 기준표는 VLOOKUP, 반복되는 이력표의 최신값은 COUNTIFS 보조열 + INDEX MATCH로 나눠 쓰면 됩니다. 이 기준만 잡아도 “분명 수정했는데 보고서에는 예전 상태가 나온다”는 실무형 오류를 훨씬 빨리 찾을 수 있습니다.