AI 해결 노트 · 2026-09-18 · 실측 2026-09-18

엑셀 두 명단 비교 — COUNTIF로 공통·한쪽에만 있는 사람 찾기, 그리고 안 맞는 경우들

한 줄로

두 명단을 대조할 때는 =COUNTIF(상대명단, 내값) 을 양쪽 시트에 다 넣어야 합니다. 한쪽만 넣으면 상대 명단에만 있는 사람은 화면에 아예 안 나옵니다. 사번 8개씩 든 더미 명단으로 돌려 보니 정답이 공통 6·A에만 2·B에만 2인데 수식은 공통 5·A에만 3·B에만 3을 내놨고, 어긋난 한 건은 사번 뒤에 붙은 공백 한 칸 때문이었습니다.

이런 분께

• 인사 명부와 교육 수료자 명단을 맞춰 「아직 안 들은 사람」을 뽑아야 하는 분
• 거래처 목록 두 개를 받아 겹치는 곳을 골라야 하는 분
• VLOOKUP을 걸었더니 #N/A가 잔뜩 나와 어디까지 믿어야 할지 모르겠는 분

실측 환경

먼저 정답을 적어 두고 시작했습니다

수식을 돌리기 전에 사람 눈으로 본 답을 로그에 박아 놨습니다. 나중에 수식 결과와 맞대 보려는 것입니다.

• 공통 6건 — A1001, A1002, A1003, A1005, A1006(A에서는 소문자 a1006), 1007
• 명단A에만 2건 — A1004, A1008
• 명단B에만 2건 — A1009, A1010

명단B에는 실무에서 자주 보는 흠을 세 군데 심었습니다. A1005 뒤에 공백 한 칸(A1005 ), 명단A에서 소문자였던 a1006을 대문자 A1006으로, 명단A에서 숫자로 저장된 사번 1007을 글자 "1007"로 넣었습니다.

양쪽에 같은 수식을 넣습니다

명단A 시트 D열에 넣은 것입니다.

=COUNTIF(명단B!$A$2:$A$9,A2)
=IF(D2=0,"상대에 없음","공통")

명단B 시트에도 방향만 바꿔 똑같이 넣습니다.

=COUNTIF(명단A!$A$2:$A$9,A2)

무엇이 어긋나는지 보려고 두 가지를 더 넣었습니다. EXACT는 대소문자까지 보고, MATCH는 셀에 숫자로 저장됐는지 글자로 저장됐는지를 봅니다.

=SUMPRODUCT(--EXACT(A2,명단B!$A$2:$A$9))
=IFERROR(MATCH(A2,명단B!$A$2:$A$9,0),"없음")

셋이 서로 다른 답을 냅니다

세 가지가 갈리는 자리를 보시면 됩니다.

공백 한 칸(A1005). 사람 눈에는 같은 사번인데 COUNTIF·EXACT·MATCH 셋 다 못 찾았습니다. 화면에 보이는 글자가 같아도 셀 안의 글자 수가 5자와 6자로 다릅니다. 명단A 쪽에서는 「B에 없음」, 명단B 쪽에서는 「A에 없음」으로 나와서, 같은 사람이 양쪽 「누락자」 명단에 동시에 올라갑니다.

대소문자(a1006). COUNTIF는 1을 세서 「공통」이라고 했고 EXACT는 0을 냈습니다. COUNTIF는 대문자와 소문자를 구별하지 않습니다. 사번이라면 이 동작이 편하지만, 대소문자로 뜻이 갈리는 코드값(제품코드 AB-1과 ab-1)을 비교할 때는 EXACT를 써야 합니다.

숫자와 글자(1007). COUNTIF는 1, MATCH는 「없음」입니다. COUNTIF는 숫자 1007과 글자 "1007"을 같은 값으로 봤지만, MATCH의 정확히 일치(세 번째 인수 0)는 그러지 않았습니다. 같은 자리에 VLOOKUP도 걸어 봤습니다.

=IFERROR(VLOOKUP(A2,명단B!$A$2:$B$9,2,FALSE),"#N/A")

1007 줄을 보십시오. COUNTIF는 「있다」고 세는데 VLOOKUP은 #N/A를 냅니다. 「명단에 있는 사람인데 왜 이름을 못 가져오냐」는 상황이 이 자리에서 생깁니다. 대소문자만 다른 a1006은 COUNTIF·MATCH·VLOOKUP 셋 다 찾아냈습니다.

수식이 센 것 vs 사람이 센 것

양쪽 다 한 건씩 어긋났고, 그 한 건은 A1005 하나입니다. 어느 방향에서 봐도 「상대에 없음」이라 두 번 잡혔습니다.

공백을 걷어내고 다시 셌습니다

양쪽에 정리용 열을 하나씩 만들어 TRIM으로 앞뒤 공백을 떼고, 그 열끼리 비교했습니다.

I2: =TRIM(A2)
J2: =COUNTIF(명단B!$I$2:$I$9,I2)

TRIM 한 번으로 정답과 맞았습니다. 대소문자가 다른 a1006/A1006과 숫자·문자로 갈린 1007은 정리 전 COUNTIF에서도 이미 일치로 세고 있어서 따로 손댈 것이 없었습니다. 명단을 받자마자 이 정리 열부터 만들어 두시는 편이 낫습니다.

셀 안의 글자 수를 세어 보면 공백이 붙었는지 바로 보입니다.

=LEN(A2)

시험 파일에서 명단A의 A1005는 5, 명단B의 A1005 는 6이었습니다. 이 한 칸 차이가 COUNTIF·EXACT·MATCH·VLOOKUP을 전부 어긋나게 한 원인입니다. 보이는 글자가 같은데 길이가 다르면 그 칸부터 의심하시면 됩니다.

실패한 것

• 정리 열을 처음 만들 때 셀 서식을 「텍스트」로 먼저 바꾼 다음 =TRIM(A2) 를 넣었습니다. 수식이 계산되지 않고 글자 그대로 남아서 COUNTIF가 8행 전부 0을 냈습니다. 서식을 지우고 다시 넣자 정상으로 돌아왔습니다. 수식을 넣을 칸에는 텍스트 서식을 미리 걸지 마십시오.
• 서식을 되돌리려고 COM으로 NumberFormat = "General" 을 넣었더니 「Range 클래스 중 NumberFormat 속성을 설정할 수 없습니다」로 거부당했습니다. 한국어 Excel에서는 같은 서식 이름이 G/표준 입니다. ClearFormats() 로 서식을 지우는 쪽으로 돌려서 해결했고, 그 뒤 NumberFormatLocal 을 읽으니 G/표준 이었습니다.
• 홈 > 조건부 서식 > 중복 값으로 두 명단을 한 화면에서 물들이는 방법은 돌려 보지 않았습니다.
• 명단B 시트에 들어간 수식 원문을 첫 로그에 안 남겨서, 나중에 따로 읽어 03_보탬시험.txt 에 붙였습니다.

확인하지 않은 것

• 사번 가운데에 공백이 들어간 경우(A10 05)는 넣어 보지 않았습니다. TRIM은 가운데 공백을 한 칸으로 줄일 뿐 없애지는 않습니다.
• 눈에 안 보이는 다른 문자(줄바꿈, 전각 공백, 유니코드 160번)는 시험 대상에 넣지 않았습니다. CLEAN이나 SUBSTITUTE가 필요한 경우가 여기에 들어갑니다.
• 명단이 수천 행일 때 COUNTIF를 두 방향으로 걸면 얼마나 느려지는지는 재지 않았습니다. 8행짜리로만 확인했습니다.
• XLOOKUP·FILTER 같은 최신 함수로 같은 일을 했을 때의 결과는 비교하지 않았습니다.
• 파워 쿼리의 병합(조인)으로 대조했을 때 공백 한 칸을 어떻게 다루는지는 확인하지 않았습니다.

함께 보기

• 엑셀 중복값 찾기·지우기
• 엑셀 IF 함수가 기준값·빈칸·0에서 갈리는 자리
• 엑셀 표 정리 순서 — 병합 셀·빈 행·문자 날짜