Google Sheets에서 기준 목록과 확인할 목록을 비교하려면, 한쪽에서만 찾을 수 있는 값을 표시하고 반대 방향도 같은 방식으로 검사하세요. 기준 목록 A의 값이 확인 목록 B에 있는지에는 COUNTIF 또는 정확 일치 MATCH(...,0)를 쓸 수 있습니다. 다만 “한 번이라도 있는가”만 확인하면 같은 코드의 수량 차이는 놓칩니다. 주문·재고·명단처럼 중복 건수까지 맞아야 한다면 고유 키마다 양쪽 출현 횟수를 계산해 비교하세요. 먼저 원본을 복사하고 ID·코드 열만 별도 범위에서 확인하면 원자료를 망가뜨리지 않고 차이를 검토할 수 있습니다.
원본 행을 바로 삭제하거나 정렬하지 마세요. 먼저 파일 또는 탭 사본에서 키 값과 결과 열을 만들고, 서로 다른 출처의 전체 행을 정렬해 섞지 않습니다. 출석·주문처럼 중복 자체가 의미 있는 자료에서는 “중복 제거”가 대조를 망칠 수 있습니다.
먼저 존재 여부, 수량, 순서 중 무엇을 대조할지 정합니다
“두 목록이 같은가?”는 상황에 따라 다른 뜻입니다. 한쪽에만 등록된 코드가 있는지 보는 대조와, 같은 코드가 같은 횟수만큼 있는지 보는 검산과, 행 순서까지 같은지 보는 비교는 각각 필요한 절차가 다릅니다.
| 목적 | 비교 기준 | 권장 방법 | 이 방법만으로 알 수 없는 것 |
|---|---|---|---|
| 회원 명단 A가 권한 명단 B에도 있는가 | 키가 한 번 이상 존재하는지 | COUNTIF 또는 MATCH를 각 방향으로 표시 | 한쪽에 같은 회원이 두 번 들어갔는지 |
| 재고 실사 수량이 시스템 수량과 맞는가 | 키별 출현 횟수 | 양쪽의 고유 키를 만든 뒤 각각 COUNTIF로 건수 계산 | 개별 행의 주문·상태·날짜까지 같은지 |
| 주문 상세 두 파일의 레코드를 대조 | 주문번호처럼 안정적인 고유키 또는 키 조합 | 단일 키는 존재 검사, 복합 키는 COUNTIFS | 키가 같아도 금액·상태 등 다른 열이 맞는지 |
| 내보낸 목록의 행 순서도 일치해야 함 | 정렬된 키와 행 위치 | 사본에서 안정적인 키로 정렬한 뒤 행별 비교 | 정렬하지 않은 원본에서의 행 대응 |
보통의 목록 대조는 셀 하나를 “있음/없음”으로 표시하면 충분합니다. 반대로 재고 수량, 입·퇴사 명단, 정산 건수처럼 같은 값이 여러 번 나타날 수 있는 작업은 다음 단계에서 중복 횟수도 비교해야 합니다.
가상 상품 코드 예제와 기대 결과
설명용으로 만든 아래 자료에서 A열은 시스템 기준 목록, B열은 확인자가 센 실사 목록입니다. 두 열은 보기 편하게 나란히 놓았을 뿐 같은 행끼리 한 쌍이라는 뜻은 아닙니다.
| A열 기준 목록 | B열 확인 목록 |
|---|---|
P-101 | P-101 |
P-102 | P-103 |
P-103 | P-104 |
P-103 | P-104 |
P-104 | P-106 |
P-105 |
존재 여부만 보면 기준 목록의 P-102와 P-105가 확인 목록에서 빠졌고, 확인 목록의 P-106은 기준표에 없습니다. 여기까지만 보면 P-103과 P-104는 양쪽에 존재하므로 정상처럼 보입니다. 수량까지 세면 기준표의 P-103은 2건인데 확인 목록은 1건, 반대로 P-104는 기준표 1건인데 확인 목록에 2건입니다. 한 방향이나 단순 존재 여부만 검사하면 이 차이를 놓칩니다.
원본을 보존하고 같은 의미의 비교 키를 정합니다
- 파일 사본을 만들거나 원본 시트를 복제합니다. 여러 사람이 쓰는 문서라면 복사본에서 작업할 것과 기준 데이터 소유자도 확인합니다.
- 양쪽 목록에서 한 개의 안정적인 키를 골라 같은 열 구조로 복사합니다. 고객명처럼 바뀌거나 중복될 수 있는 표시값보다 고객 ID·주문번호·상품 코드처럼 의미가 고정된 키를 사용합니다.
- 머리글을 제외하고 같은 시작 행을 정합니다. 아래 예제는 A2:A1000과 B2:B1000을 사용하므로 실제 마지막 행이 1000을 넘으면 두 범위를 함께 넓힙니다.
- 빈칸, 잘못된 코드, 중복 키를 먼저 훑어봅니다. 이 가이드는 빈 키를 정상 항목으로 간주하지 않습니다. 필수 키가 비어 있는 행은 별도의 오류로 표시해 담당자에게 확인합니다.
- 선행 0이 중요한 코드라면 두 원본에서 텍스트로 보존됐는지 확인합니다. 표시 형식을 바꿔도 이미 숫자로 변환되어 없어진 0이 되살아나는 것은 아닙니다.
키를 정하는 간단한 질문: 값 하나만 보고 같은 항목이라고 판단할 수 있나요? 이름이 같은 고객이 여러 명일 수 있다면 이름 대신 고객 번호를 씁니다. 주문번호가 채널마다 반복된다면 주문번호와 채널, 또는 주문 상세 고유 ID를 함께 써야 합니다.
기준 목록에서 확인 목록에 없는 값 찾기
A열이 기준, B열이 확인 목록일 때 C2에 아래 수식을 넣고 데이터가 있는 마지막 행까지 채우면 됩니다. A2가 빈 행이면 결과도 비워두고, 값이 B열에 한 번도 없을 때만 “확인 목록에 없음”으로 표시합니다.
=IF(A2="","",IF(COUNTIF($B$2:$B$1000,A2)=0,"확인 목록에 없음","있음"))COUNTIF는 지정 범위에서 기준값이 몇 번 나타나는지 셉니다. 결과가 0이면 존재하지 않고 1 이상이면 한 번 이상 존재합니다. 이 수식은 대조 여부를 표시할 뿐, 다른 열의 값이 맞는지 또는 같은 키의 횟수가 맞는지까지 판정하지는 않습니다.
정렬되지 않은 목록을 대상으로 정확한 위치를 검사하고 싶다면 MATCH 세 번째 인수에 0을 넣어야 합니다.
=IF(A2="","",IF(ISNA(MATCH(A2,$B$2:$B$1000,0)),"확인 목록에 없음","있음"))MATCH는 값 자체가 아니라 범위 안에서의 위치를 반환합니다. 0은 정확 일치이며, 정렬되지 않은 범위에 필요한 옵션입니다. 이 검색 순서 옵션을 생략하면 기본값 1이 적용돼 범위가 오름차순이라고 가정하는 근사 검색이 될 수 있습니다.
확인 목록에서 기준 목록에 없는 값도 찾습니다
반대쪽 차이는 같은 검사를 한 번 더 해야 발견됩니다. D2에서 B열의 값이 기준 목록 A에 있는지 확인합니다.
=IF(B2="","",IF(COUNTIF($A$2:$A$1000,B2)=0,"기준 목록에 없음","있음"))이제 C열에서 “확인 목록에 없음”으로 필터하면 빠진 항목, D열에서 “기준 목록에 없음”으로 필터하면 기준에 등록되지 않은 추가 항목이 보입니다. 기준 목록이 맞다는 업무 규칙이 없다면 빠진 쪽만 찾고 끝내지 마세요. 상대 목록에만 있는 값은 잘못된 파일을 합쳤거나 기간·상태 조건이 달랐다는 단서일 수 있습니다.
간단한 합계 검산도 같이 합니다. 각 범위의 비어 있지 않은 행 수는 =COUNTA(A2:A1000), =COUNTA(B2:B1000)으로 볼 수 있습니다. 행 수가 같아도 서로 다른 키가 빠지고 추가된 차이가 서로 상쇄될 수 있으므로, 같은 행 수만으로 두 목록이 일치한다고 결론 내리면 안 됩니다.
출현 횟수가 중요한 자료는 고유 키별 수량을 비교
중복 수량을 세려면 두 열의 키를 한 열로 쌓고, 빈값을 뺀 다음 고유값 목록을 만듭니다. 아래 수식은 E2에 입력하며 원본은 A2:A1000과 B2:B1000입니다.
=UNIQUE(FILTER({A2:A1000;B2:B1000},{A2:A1000;B2:B1000}<>""))이 배열 수식은 A와 B를 세로로 이어 붙인 뒤 빈 셀을 제외하고 중복 없는 키를 첫 등장 순서로 반환합니다. 시트의 지역 설정에 따라 함수 인수 구분 기호가 다르게 보일 수 있습니다. 값이 둘 다 비어 있으면 FILTER 결과가 없다는 #N/A가 나오므로, 먼저 원본에 실제 키가 있는지 확인하세요.
F2에 기준 수량, G2에 확인 수량을 세고 H2에서 차이를 분류합니다. F2:H2 수식을 E열의 키 목록 옆으로 필요한 행만큼 채웁니다.
F2: =COUNTIF($A$2:$A$1000,E2)
G2: =COUNTIF($B$2:$B$1000,E2)
H2: =IF(F2=G2,"수량 일치",IF(F2>G2,"확인 목록 "&(F2-G2)&"개 부족","확인 목록 "&(G2-F2)&"개 초과"))| 고유 키 | 기준 수량 | 확인 수량 | 판정 |
|---|---|---|---|
| P-101 | 1 | 1 | 수량 일치 |
| P-102 | 1 | 0 | 확인 목록 1개 부족 |
| P-103 | 2 | 1 | 확인 목록 1개 부족 |
| P-104 | 1 | 2 | 확인 목록 1개 초과 |
| P-105 | 1 | 0 | 확인 목록 1개 부족 |
| P-106 | 0 | 1 | 확인 목록 1개 초과 |
기준 행 수는 6개, 확인 행 수는 5개지만, 전체 행 수 차이만으로는 어떤 키가 부족한지 알 수 없습니다. 키별 표에서 한 건씩 어디가 다른지 확인한 뒤에만 원본 정정이나 재수집을 요청하세요. 이 절차는 같은 키의 건수 차이를 찾으며 각 주문 금액이나 상품 상태까지 비교하지는 않습니다.
키에 대소문자 차이를 별도 값으로 보존해야 하거나 리터럴 *·?가 들어 있다면 COUNTIF로 일치 여부나 수량을 확정하지 마세요. COUNTIF는 대소문자를 구분하지 않고 별표와 물음표를 와일드카드로 처리합니다. 문자 하나까지 구분하는 존재 검사는 다음처럼 계산합니다.
=IF(A2="","",IF(SUMPRODUCT(--EXACT(A2,$B$2:$B$1000))=0,"확인 목록에 없음","있음"))같은 규칙으로 고유 키별 출현 횟수를 세려면 아래 두 수식을 씁니다.
=SUMPRODUCT(--EXACT(E2,$A$2:$A$1000))
=SUMPRODUCT(--EXACT(E2,$B$2:$B$1000))첫 수식은 E2와 대소문자·공백·숨겨진 문자가 모두 같은 A열 항목 수를, 둘째는 B열 항목 수를 계산합니다. Google Sheets의 EXACT는 두 문자열의 문자가 대소문자와 공백, 숨겨진 문자까지 동일한지 확인합니다. 정확한 대소문자 별도 키가 있다면 고유 목록에도 각각의 표기를 별도 행으로 준비하고, 결과가 두 표기를 합치지 않는지 확인합니다.
여러 열을 함께 봐야 하면 COUNTIFS로 복합 키를 만듭니다
주문번호 하나가 여러 날짜나 판매 채널에서 반복된다면, 주문번호 한 열만 비교해선 서로 다른 레코드를 같은 값으로 오판할 수 있습니다. 예를 들어 A열 주문번호와 B열 채널 조합을, D열 주문번호와 E열 채널 조합에 대조하려면 아래처럼 조건 쌍을 분리합니다.
=IF(A2="","",IF(COUNTIFS($D$2:$D$1000,A2,$E$2:$E$1000,B2)=0,"복합 키 없음","있음"))COUNTIFS는 여러 범위가 모든 지정 조건을 동시에 충족하는 행의 수를 셉니다. 각 조건 범위는 같은 크기를 사용하세요. 여러 값을 &로 이어 만든 임시 문자를 키로 쓰면 원본 필드 안에 연결 문자가 들어올 때 서로 다른 조합이 같은 문자열이 될 수 있습니다. 가능한 경우 각 필드를 별도 조건으로 전달하세요.
기준·확인 쪽에 동일 조합이 여러 번 나타날 수 있다면, 대조 목록의 두 조건을 반대로 놓은 횟수도 확인합니다. 복합 키의 존재 여부를 찾는 것과 복합 키별 수량을 대조하는 것은 모두 별도 결과로 남깁니다.
공백·대소문자·숫자와 텍스트는 비교 규칙부터 통일
눈에 보이는 값이 같아도 앞뒤 공백, 줄바꿈 없는 공백, 숨은 문자, 숫자와 문자 저장 차이 때문에 비교 결과가 달라질 수 있습니다. 원본 열을 직접 덮어쓰기 전에 보조 열에 정규화 결과를 만들어 양쪽에 똑같은 규칙을 적용합니다.
- 일반 앞뒤 공백:
TRIM은 텍스트의 앞·뒤와 반복되는 공백을 정리합니다. 다만 단어 사이 반복 공백도 하나로 줄이므로 공백이 식별 의미의 일부인지 먼저 확인합니다. - 줄바꿈 없는 공백: 복사한 웹 자료의 특수 공백은 일반
TRIM으로 제거되지 않을 수 있습니다. 보조 열에서=TRIM(SUBSTITUTE(A2,CHAR(160)," "))처럼 바꿔 실제 결과를 확인하고, 같은 처리를 양쪽 열에 적용합니다. - 대소문자: 일반 목록 코드가 대소문자를 구분하지 않아도 되는지 업무 규칙으로 정합니다.
COUNTIF는 대소문자를 구분하지 않으므로, 구분해야 한다면 위의EXACT계산을 사용합니다. - 텍스트와 숫자:
00123같은 ID를 숫자로 가져와 앞의 0이 사라졌다면 표시 형식을 바꾸는 것만으로 복원되지 않습니다. 원본을 다시 가져오거나 정해진 자릿수 규칙을 확인하고 텍스트로 맞춥니다. - 날짜: 실제 날짜 값과 “2026-10-05”라는 텍스트가 섞이지 않았는지 확인합니다. 화면에 보이는 표시 형식만 같게 만들어도 저장된 값이 동일하다고 보장할 수 없습니다.
정규화는 차이를 지우는 작업입니다. 직원 ID의 대소문자나 상품 코드 중간 공백이 실제 구분 기준인데 모두 지우면 잘못 일치할 수 있습니다. 먼저 원본 비교 결과를 저장하고, 어떤 값을 같은 항목으로 취급할지 담당자와 합의한 후에만 변환 열을 사용하세요.
불일치 결과만 모으고 행 수를 마지막까지 맞춥니다
결과를 새 표로 뽑고 싶다면, 표시 열을 기준으로 시트의 필터를 켜고 “확인 목록에 없음”만 선택할 수 있습니다. 별도 영역에 동적 결과를 만들려면 Google Sheets의 FILTER 함수를 쓸 수 있습니다.
=IFNA(FILTER(A2:C1000,C2:C1000="확인 목록에 없음"),"누락된 키 없음")여기서 A:C는 원본 키와 필요한 설명 열, C는 앞 단계에서 만든 상태 열입니다. FILTER의 조건 범위는 필터링하는 행 범위와 높이가 같아야 합니다. 일치 행이 하나도 없으면 #N/A를 돌려주므로 IFNA가 표시 문구로 바꿉니다. 특정 키 하나가 오류인 경우와 “해당 결과가 하나도 없다”를 혼동하지 않도록 입력·범위를 검수한 뒤에 적용하세요.
- 원본 기준 행 수, 빈 키 수, 확인 대상 행 수를 별도 셀에 기록합니다.
- 불일치 표에서 누락과 추가를 각각 필터링합니다.
- 키별 수량을 계산했다면 기준 수량과 확인 수량의 합도 각각 원본 비어 있지 않은 행 수와 맞는지 확인합니다.
- 대표 키, 중복이 있는 키, 양쪽에만 있는 키를 표본으로 원본까지 역추적합니다.
- 불일치 원인과 기준 담당자가 확인되기 전에는 원본 행을 지우거나 자동으로 합치지 않습니다.
필터로 행을 숨기는 것과 값을 삭제하는 것은 다릅니다. 정렬이 필요하면 필터로 숨겨진 행이 아닌 전체 원본을 움직일 수 있으므로, 대조 결과 표나 시트 사본에서 정렬하세요.
값이 있어 보이는데 ‘없음’이라고 나올 때
| 증상 | 가능한 원인 | 확인과 조치 |
|---|---|---|
| 값이 같은데 양쪽이 불일치 | 앞뒤 공백, NBSP, 숨은 문자, 숫자·텍스트 차이 | 원본을 유지한 보조 열에서 실제 값과 자료형을 확인하고 같은 규칙으로 정규화 |
| 정렬했는데도 MATCH 결과가 이상함 | MATCH 세 번째 인수를 생략해 기본 근사 일치가 적용 | 정렬되지 않은 목록이면 세 번째 인수를 0으로 지정 |
COUNTIF가 비슷한 코드를 있다고 판정 | 문자열에 * 또는 ?가 있고 와일드카드로 해석 | 리터럴 문자를 찾으려면 이스케이프하거나 EXACT 방식으로 확인 |
| 누락 목록은 맞는데 수량이 불일치 | 한 번 이상 존재하는지와 중복 건수 대조를 혼동 | 고유 키별로 A·B 출현 횟수를 각각 계산 |
| 일부 행만 결과 표시가 비어 있음 | 비교 키가 빈 셀이고 수식이 빈값 결과를 반환 | 필수 키 누락 수를 별도 집계하고 담당자에게 확인 |
| 새 행이 추가된 뒤 누락 결과가 그대로임 | 고정 범위가 새 행을 포함하지 않음 | 두 목록의 끝 행을 확인하고 양쪽 수식 범위를 함께 확장 |
| 일치 건수는 많은데 실제 내용이 틀림 | 잘못된 키 열, 이름 중복, 복합키 생략 | 고유 ID와 필요한 조건 열을 다시 선택하고 다른 필드도 별도 검산 |
명단·재고·주문 대조별 마지막 점검
| 업무 | 추천 키 | 완료 전에 확인할 것 |
|---|---|---|
| 회원·권한 명단 | 고객·계정 ID | 기준→확인, 확인→기준을 모두 검사하고 퇴사·비활성 계정 규칙 확인 |
| 창고 실사 | 상품 코드, 필요하면 창고 위치 | 존재 여부뿐 아니라 코드별 수량·위치별 수량 합계를 비교 |
| 정산·주문 내역 | 주문 상세 ID 또는 주문번호+품목+채널 | 수량 외에도 취소·반품·금액·상태가 맞는지 별도 비교 |
| 출석·예약 명단 | 신청 ID 또는 행사 ID+신청자 ID | 중복 신청을 지우기 전에 중복이 실제 별도 회차인지 확인 |
비교가 끝나면 기준 파일 이름과 확인 파일의 내보낸 날짜, 사용한 키 열, 정규화 규칙, 남은 불일치 수를 기록합니다. 나중에 목록이 갱신되었을 때 이전 대조 결과를 현재 상태로 오해하지 않도록 출처와 기준 시점을 남기는 것이 좋습니다.
자주 묻는 질문
한쪽 목록에서 빠진 값만 찾으면 되나요?
아닙니다. A에만 있는 값과 B에만 있는 값은 다른 목록을 한 번씩 기준으로 놓아야 모두 나옵니다. 두 방향을 각각 확인하세요.
COUNTIF와 MATCH 중 무엇을 쓰면 좋나요?
COUNTIF는 출현 횟수까지 한 번에 알 수 있어 읽기 쉽습니다. MATCH는 범위 안에서 위치가 필요한 조회에 적합하며, 정렬되지 않은 데이터에서 정확히 찾으려면 세 번째 인수를 0으로 둡니다.
두 목록의 행 수가 같으면 같은 데이터인가요?
아닙니다. 서로 다른 값 하나씩이 빠지고 추가되어 전체 수가 우연히 같을 수 있습니다. 고유 키 존재와 필요 시 키별 횟수를 비교해야 합니다.
COUNTIF로 대소문자까지 구분할 수 있나요?
Google Sheets의 COUNTIF는 대소문자를 구분하지 않습니다. 식별자가 대소문자를 구분한다면 EXACT 결과를 SUMPRODUCT로 세고, 대소문자 표기마다 고유 키를 별도로 둡니다.
중복 제거 메뉴로 먼저 정리하면 더 간단하지 않나요?
중복은 실수일 수도 있지만 반복 출석·동일 품목의 여러 주문처럼 의미 있는 건수일 수도 있습니다. 먼저 원본을 보존하고 중복 개수를 비교한 뒤, 데이터 규칙을 확인하고 제거하세요.
비교가 끝나면 원본에 누락값을 자동으로 추가해도 되나요?
자동 수정 전에 차이가 발생한 이유와 기준 목록의 권위를 확인해야 합니다. 우선 불일치 보고서로 남기고, 기준 담당자의 승인이나 검수 절차를 거친 뒤 원본을 고칩니다.
Google 공식 도움말
- COUNTIF 함수 — 한 범위의 조건별 개수, 와일드카드, 대소문자 구분 한계
- MATCH 함수 — 정확 일치에 0을 지정하고 상대 위치 확인
- COUNTIFS 함수 — 여러 범위와 조건을 동시에 비교
- UNIQUE 함수 — 원본 순서대로 고유 행 만들기
- FILTER 함수 — 조건에 맞는 행만 반환하고 조건 범위 크기 맞추기
- EXACT 함수 — 대소문자·공백·숨겨진 문자를 포함해 문자열 비교
- TRIM 함수 — 앞뒤·반복 공백 제거 및 줄바꿈 없는 공백 주의
- CHAR 함수 — 문자 코드 160의 공백을 보조 열에서 확인
- Google Sheets에서 배열 사용 — 중괄호와 세미콜론으로 두 범위를 한 열에 연결