Google 스프레드시트에서 두 목록 비교하는 방법 (일치 항목과 차이)
Google 스프레드시트에는 두 목록을 비교하는 데 필요한 기능이 모두 있습니다. 양쪽에 있는 값을 강조하거나, 빠진 값을 표시하거나, 자동으로 업데이트되는 별도 목록으로 차이를 뽑아낼 수 있습니다. 이 가이드는 간단한 강조부터 열 전체를 한 번에 처리하는 수식까지 가장 유용한 방법을 소개합니다.
모든 예제는 목록 A가 A열, 목록 B가 B열에 있고, 2행부터 시작하며 1행이 머리글이라고 가정합니다. B2:B처럼 끝을 지정하지 않은 범위는 아래의 모든 행을 포함하므로 새 항목도 자동으로 반영됩니다.
한 번만 비교하면 된다면 두 목록을 무료 두 목록 비교 도구에 붙여넣으세요. 수식 없이 A에만, B에만, 양쪽 모두에 있는 항목을 확인할 수 있습니다.
1. 조건부 서식으로 일치 항목 강조
- A2:A를 선택합니다.
- 서식 → 조건부 서식을 엽니다.
- 형식 규칙에서 맞춤 수식을 선택하고 다음을 입력합니다.
=COUNTIF($B$2:$B,$A2)>0
- 채우기 색을 고르고 완료를 클릭합니다.
A 중 B에 없는 값을 강조하려면 대신 다음을 사용합니다.
=COUNTIF($B$2:$B,$A2)=0
B2:B에도 열 문자를 바꾼 규칙을 추가하면 목록 B에도 색을 칠할 수 있습니다. 수식은 범위의 첫 셀($A2)을 기준으로 작성하며, 다른 행은 스프레드시트가 자동으로 조정합니다.
2. COUNTIF나 MATCH로 각 항목 표시
보조 열을 쓰면 정렬하고 필터링할 수 있는 결과를 얻습니다. C2에 입력하세요.
=IF(COUNTIF($B$2:$B,A2)>0,"일치","불일치")
MATCH를 쓰면 TRUE 또는 FALSE를 반환합니다.
=ISNUMBER(MATCH(A2,$B$2:$B,0))
수식을 아래로 끌어 채운 뒤 데이터 → 필터 만들기로 일치 항목이나 차이만 표시하세요.
3. 수식 하나로 열 전체 확인 (ARRAYFORMULA)
수식을 모든 행에 복사하는 대신, C2에 ARRAYFORMULA 하나만 넣으면 열 전체가 채워지고 나중에 추가한 행도 처리됩니다.
=ARRAYFORMULA(IF(A2:A="",,IF(COUNTIF(B2:B,A2:A)>0,"일치","불일치")))
IF(A2:A="",,…) 부분은 빈 행을 "불일치"로 채우지 않고 빈칸으로 둡니다.
4. 일치 항목이나 차이를 새 목록으로 추출 (FILTER)
FILTER는 어느 한 목록이 바뀌면 자동으로 업데이트되는 목록을 반환합니다. A 중 B에도 있는 값:
=FILTER(A2:A,A2:A<>"",COUNTIF(B2:B,A2:A)>0)
A 중 B에 없는 값:
=FILTER(A2:A,A2:A<>"",COUNTIF(B2:B,A2:A)=0)
A2:A<>"" 조건이 중요합니다. 이 조건이 없으면 A의 빈 행이 B의 빈 행과 "일치"로 계산됩니다. 중복을 없애고 정렬하려면 UNIQUE와 SORT로 감싸세요.
=SORT(UNIQUE(FILTER(A2:A,A2:A<>"",COUNTIF(B2:B,A2:A)=0)))
수식의 A와 B를 바꾸면 목록 B에만 있는 값을 얻습니다.
5. XLOOKUP이나 VLOOKUP으로 일치 데이터 가져오기
목록 B에 관련 데이터(예: C열의 가격)가 있다면 XLOOKUP이 일치하는 항목마다 그 값을 반환합니다.
=XLOOKUP(A2,$B$2:$B,$C$2:$C,"찾을 수 없음")
VLOOKUP도 사용할 수 있습니다. 정확히 일치하도록 마지막 인수는 항상 FALSE로 지정하세요.
=IFERROR(VLOOKUP(A2,$B$2:$C,2,FALSE),"찾을 수 없음")
6. 두 열을 행별로 비교
목록이 나란히 정렬되어 있고 각 행이 옆 셀과 같은지 알고 싶을 때:
=IF(A2=B2,"같음","다름")
=는 대소문자를 구분하지 않습니다. 구분하려면 EXACT를 사용하세요.
=IF(EXACT(A2,B2),"같음","다름")
다른 행에 색을 칠하려면 A2:B를 선택하고 맞춤 수식 =$A2<>$B2로 조건부 서식 규칙을 추가하세요.
7. 서로 다른 시트에 있는 목록 비교
일반 셀에서는 다른 시트를 바로 참조할 수 있습니다.
=IF(COUNTIF(시트2!$A$2:$A,A2)>0,"일치","불일치")
조건부 서식 규칙은 다른 시트를 직접 참조할 수 없으므로 범위를 INDIRECT로 감싸세요.
=COUNTIF(INDIRECT("시트2!A2:A"),$A2)>0
시트2는 실제 시트 이름으로 바꾸세요. 이름에 공백이 있으면 작은따옴표로 감쌉니다: INDIRECT("'기존 고객'!A2:A").
자주 있는 문제와 해결 방법
- 불필요한 공백: "Apple "과 "Apple"은 일치하지 않습니다. 데이터를 선택하고 데이터 → 데이터 정리 → 공백 제거를 사용하세요.
- 한 목록 안의 중복: 먼저 데이터 → 데이터 정리 → 중복 항목 삭제를 쓰거나 결과를
UNIQUE로 감싸세요. - 대소문자: COUNTIF, MATCH, XLOOKUP, VLOOKUP은 대소문자를 구분하지 않습니다. 대소문자를 구분해 존재 여부를 확인하려면
=SUMPRODUCT(--EXACT($B$2:$B$100,A2))>0을 사용하세요. - 숫자와 텍스트: 숫자와 같은 숫자를 텍스트로 저장한 값은 일치하지 않습니다. 서식 → 숫자로 두 열을 같은 형식으로 맞추거나
=VALUE(A2)로 변환하세요.
요약
| 방법 | 결과 | 적합한 경우 |
|---|---|---|
| 조건부 서식(COUNTIF) | 일치·차이 색칠 | 빠른 육안 확인 |
| COUNTIF / MATCH 보조 열 | 일치 / 불일치 표시 | 정렬과 필터링 |
| ARRAYFORMULA | 열 전체 표시 | 계속 늘어나는 목록 |
| FILTER(+ UNIQUE, SORT) | 일치·차이의 자동 업데이트 목록 | 결과 추출 |
| XLOOKUP / VLOOKUP | 다른 목록의 관련 데이터 | 다른 열도 필요한 대조 |
행별 비교(= / EXACT) |
행마다 같음 / 다름 | 정렬된 두 열 |
| 조건부 서식의 INDIRECT | 시트 간 강조 | 다른 탭에 있는 목록 |
Excel을 쓰고 있다면 Excel에서 두 목록 비교하는 방법을 참고하세요. 수식 없이 한 번만 확인하려면 온라인 목록 비교 도구를 사용하세요.