두 열에 담긴 데이터를 서로 비교해 차이점을 확인해야 하는 상황은 실무에서 생각보다 자주 발생합니다. 엑셀은 두 개의 목록을 비교하고 차이를 반환하는 다양한 기능과 수식을 제공하는데요. 이번 글에서는 엑셀에서 두 열을 비교하여 차이점을 찾는 대표적인 방법들을 단계별로 자세히 소개하겠습니다.
엑셀에서 두 열을 비교해 차이점을 찾는 7가지 방법
이 섹션에서는 엑셀에서 두 열을 비교하여 차이점을 찾는 7가지 방법을 하나씩 살펴보겠습니다. 먼저 간단한 예제를 통해 전체적인 흐름을 익혀보겠습니다.
위 그림에는 과일 이름이 담긴 두 개의 목록이 준비되어 있습니다. 이 두 목록(List-1, List-2)을 비교하여 차이점을 찾는 것이 목표입니다. 아래에서 소개하는 모든 방법에는 동일한 표를 사용합니다.
1. 조건부 서식으로 두 열 비교하기
조건부 서식(Conditional Formatting)을 활용하면 두 열의 고유값을 시각적으로 강조 표시할 수 있습니다. 절차는 매우 간단합니다.
📌단계:
- 먼저 조건부 서식을 적용할 범위를 선택합니다. 이 예제에서는 B5:B11입니다.
- 홈 탭에서 조건부 서식을 클릭한 뒤, 셀 규칙 강조 아래의 중복 값을 클릭합니다.
- 중복 값 대화상자에서 중복을 선택하면 두 열에 공통으로 나타나는 값이 표시됩니다.
- 반대로 고유를 선택하면 두 열에서 한 번만 나타나는 고유값이 표시됩니다.
- 확인 버튼을 누르면 조건부 서식이 적용됩니다.
함께 읽으면 좋은 글: 엑셀에서 두 열 또는 두 목록 비교하기
2. IF 함수로 두 열 비교하기
IF 함수를 사용하면 두 열의 값이 일치하는지 여부를 간단하게 판별할 수 있습니다. 아래 단계를 따라 해 보세요.
📌단계:
- 먼저 List-1의 과일이 List-2에 있는지 표시할 새로운 열을 만듭니다.
- 새로 만든 열의 첫 번째 셀(예: E5)을 선택하고 아래 수식을 입력합니다.
=IF(B5=C5,"YES","NO")
수식 구성 요소:
- B5 = List-1의 과일
- C5 = List-2의 과일
- ENTER 키를 누르면 해당 셀에 결과가 표시됩니다.
- 이후 채우기 핸들(Fill Handle) 도구를 이용해 수식을 아래까지 드래그하여 자동으로 채웁니다.
- 모든 셀에 결과가 표시되어 두 열의 차이를 한눈에 파악할 수 있습니다.
3. EXACT 함수로 열 비교하기
EXACT 함수는 두 텍스트 문자열을 비교하여 정확히 일치하면 TRUE, 일치하지 않으면 FALSE를 반환합니다. 대소문자까지 구분하기 때문에 정밀한 비교가 필요할 때 유용합니다.
📌단계:
- 먼저 빈 셀을 선택하고 아래 수식을 입력합니다.
=EXACT(B5,C5)
수식 구성 요소:
- B5 = List-1의 과일
- C5 = List-2의 과일
- ENTER 키를 누르면 셀에 FALSE가 반환됩니다.
- 이후 수식을 아래로 드래그하면 각 행의 비교 결과가 표시됩니다.
4. IF 함수와 AND 함수 조합하기
IF 함수와 AND 함수를 조합하면 두 값이 일치하지 않을 때 별도의 문구를 표시하도록 설정할 수 있습니다.
📌단계:
- 먼저 원하는 셀에 아래 수식을 입력합니다.
=IF(AND(B5<>C5),"No Match","Match")
수식 구성 요소:
- B5 = List-1의 과일
- C5 = List-2의 과일
- 이후 수식을 아래로 드래그하면 각 셀에 비교 결과가 나타납니다.
5. IF, ISNA, VLOOKUP 함수 조합하기
IF, ISNA, VLOOKUP 함수를 조합하면 한 목록의 값이 다른 목록에 존재하는지 여부를 확인할 수 있습니다.
📌단계:
- 먼저 새로운 열을 만들고, 첫 번째 셀(예: E5)에 아래 수식을 입력합니다.
=IF(ISNA(VLOOKUP(B5,$C$5:$C$11,1,0)),"NO","YES")
수식 구성 요소:
- B5 = 찾을 값(Lookup Value)
- C5:C11 = 검색 범위(Lookup Array)
💡 수식 분석
VLOOKUP(B5,$C$5:$C$11,1,0)은 B5의 값(즉, Apple)을 $C$5:$C$11 범위에서 찾습니다. 해당 값이 검색 범위에 없으므로 #N/A 오류를 반환합니다.
ISNA 함수는 셀에 #N/A! 오류가 있는지 확인하고, 오류의 유무에 따라 TRUE 또는 FALSE를 반환합니다.
따라서 ISNA(VLOOKUP(B5,$C$5:$C$11,1,0)) = ISNA(#N/A)는 TRUE를 반환합니다.
마지막으로 IF(ISNA(VLOOKUP(B5,$C$5:$C$11,1,0)),"NO","YES") = IF(TRUE,"NO","YES") = NO가 됩니다.
즉, 최종 출력값 => NO. List-1의 과일 'Apple'이 List-2에 존재하지 않기 때문입니다.
- ENTER 키를 누르면 해당 셀에 NO가 표시됩니다.
- 채우기 핸들 도구로 수식을 아래까지 드래그하여 자동으로 채웁니다.
- 완료되면 List-1과 List-2 사이의 차이를 확인할 수 있습니다.
함께 읽으면 좋은 글: VLOOKUP으로 엑셀 두 열 비교하기
6. IF, ISERROR, MATCH 함수 조합하기
이번에는 IF, ISERROR, MATCH 함수를 사용하여 두 열을 비교해 보겠습니다. 이 수식은 List-1의 항목 중 List-2에 없는 과일 이름만 골라서 반환합니다.
📌단계:
- 먼저 새로 만든 열의 첫 번째 셀 D5를 선택하고 아래 수식을 입력합니다.
=IF((ISERROR(MATCH(B5,$C$5:$C$11,0))),B5,"")
수식 구성 요소:
- B5 = 찾을 값(Lookup Value)
- C5:C11 = 검색 범위(Lookup Array)
💡 수식 분석
MATCH 함수는 B5의 값(즉, Apple)을 검색 범위 $C$5:$C$11에서 찾습니다.
검색 범위에서 값을 찾지 못했으므로 MATCH(B5,$C$5:$C$11,0)은 #N/A를 반환합니다.
이때 ISERROR(MATCH(B5,$C$5:$C$11,0))= ISERROR(#N/A)는 TRUE를 반환합니다.
마지막으로 IF((ISERROR(MATCH(B5,$C$5:$C$11,0))),B5,"") = IF(TRUE,B5,"")는 B5의 값(즉, Apple)을 반환합니다.
따라서 최종 출력값 => Apple.
- ENTER 키를 누르면 결과가 표시됩니다. 이후 수식을 아래 셀로 드래그합니다.
- 수식이 복사된 셀들에 비교 결과가 표시됩니다.
- 같은 방식으로 List-2에만 있는 과일 이름도 찾을 수 있습니다. 이 경우 수식은 아래와 같습니다.
=IF((ISERROR(MATCH(C5,$B$5:$B$11,0))),C5,"")
수식 구성 요소:
- C5 = 찾을 값(Lookup Value)
- B5:B17 = 검색 범위(Lookup Array)
7. IF 함수와 COUNTIF 함수 조합하기
이 방법에서는 List-1에 있지만 List-2에는 없는 과일 이름이 있을 경우, "List-2에서 찾을 수 없음"이라는 메시지를 표시합니다. IF 함수와 COUNTIF 함수를 조합하여 구현합니다.
📌단계:
- 먼저 셀 D5에 아래 수식을 입력합니다.
=IF(COUNTIF($C$5:$C$11, $B5)=0, "Not Found in List-2", "")
💡 수식 분석
COUNTIF 함수는 지정된 범위 내에서 특정 값이 몇 개인지 세어 반환합니다.
COUNTIF($C$5:$C$11, $B5)는 B5의 값(즉, Apple)을 $C$5:$C$11 범위에서 찾지만, 범위 내에 해당 값이 없으므로 출력값 => 0.
마지막으로 IF(COUNTIF($C$5:$C$11, $B5)=0, "Not Found in List-2", "")는 조건이 0(찾지 못함)일 때 "List-2에서 찾을 수 없음"을 반환하고, 그렇지 않으면 셀을 비워둡니다("").
따라서 최종 출력값 => "List-2에서 찾을 수 없음".
- 이제 ENTER 키를 눌러 결과를 확인합니다.
- 이후 수식을 아래로 드래그합니다.
- 이렇게 하면 두 열 사이의 차이를 한눈에 확인할 수 있습니다.
마무리
지금까지 엑셀에서 두 열을 비교하여 차이점을 찾는 다양한 방법을 살펴보았습니다. 같은 원리를 응용하면 일치하는 항목을 찾는 데에도 활용할 수 있습니다. 소개한 방법 중에서는 조건부 서식이 가장 추천할 만합니다. 여러 열을 한꺼번에 비교할 수 있을 뿐만 아니라, 절차가 간단하고 빠르며 일치 항목과 차이점을 모두 확인할 수 있기 때문입니다.
이 글이 도움이 되었기를 바랍니다. 더 유용한 글들은 저희 웹사이트에서 확인하실 수 있습니다. 끝까지 읽어주셔서 감사합니다!
더 읽어볼 만한 글
- 엑셀 조건부 서식으로 두 열 비교하기
- 엑셀에서 두 열의 텍스트 비교하기 (7가지 유용한 방법)
- 엑셀에서 두 셀의 텍스트 비교하기 (9가지 예제)
- 두 열을 비교하고 값 반환하는 엑셀 수식 (5가지 예제)