VLOOKUP을 사용하여 Excel에서 두 열을 비교하는 방법

Excel에서 대규모 데이터 세트로 작업할 때 유사점이나 차이점을 찾기 위해 두 열을 비교해야 할 수 있습니다.

VLOOKUP은 두 열 사이에서 일치하는 데이터를 검색할 수 있는 강력한 기능입니다. 이 기능을 사용하면 다른 열에 나타나는 한 열의 값을 검색할 수 있습니다.

이 문서에서는 데이터를 효율적으로 분석할 수 있도록 VLOOKUP을 사용하여 두 열을 비교하는 방법을 보여줍니다.

목차

• Vlookup 기능의 기초
  • Vlookup 기능
• VLOOKUP용 데이터 준비
  • 데이터 포맷
• VLOOKUP을 사용하여 두 열을 비교하는 방법
  • 기타 오류 처리
• 열 비교를 위한 VLOOKUP의 대안
  • 1. 인덱스 및 일치 기능 사용
  • 2. Excel에서 XLOOKUP 사용
• VLOOKUP 성능을 향상시키는 5가지 팁
  • 1. 조회 범위 제한
  • 2. 절대 참조 사용
  • 3. 데이터 정렬
  • 4. 이중 VLOOKUP 사용
  • 5. 조건부 서식 사용
• VLOOKUP의 고급 사용
• 우리의 마지막 말

Vlookup 기능의 기초

A 열과 B 열에 두 개의 항목 목록이 있는 스프레드시트가 있다고 가정합니다. 목록 2에도 나타나는 목록 1의 항목을 찾으려고 합니다.

수천 개의 항목 목록을 통해 수동으로 작업하는 것은 엄청난 시간이 소요될 것이라고 상상할 수 있습니다. 고맙게도 Excel은 VLOOKUP을 구출합니다!

VLOOKUP이라는 용어는 수직 조회를 나타냅니다. 이 함수는 두 열을 비교하고 두 열 사이에서 일치하는 항목을 찾은 다음 관련 값을 반환합니다.

Vlookup 기능

다음은 VLOOKUP 함수의 기본 구문입니다.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

다음은 요소의 분석입니다.

• lookup_value : table_array의 첫 번째 열에서 검색하려는 값입니다.

• table_array : 검색하려는 데이터가 포함된 셀 범위입니다.

• col_index_num : 값을 반환하려는 table_array의 열 번호입니다.

• range_lookup : 선택 사항입니다. TRUE(대략 일치) 또는 FALSE(완전 일치)입니다. 기본값은 참입니다.

VLOOKUP용 데이터 준비

VLOOKUP을 사용하여 Excel에서 두 열을 비교하기 전에 데이터를 준비해야 합니다.

값을 비교하려는 워크시트에 별도의 열 두 개를 만듭니다. 이 문서에서는 예제에 A열과 B열을 사용합니다.

다음은 이 문서에서 사용된 샘플 데이터입니다.

다음은 "VLOOKUP용 데이터 준비" 섹션에 추가할 헤더가 있는 샘플 테이블입니다.

목록 1 목록 2
항목 1 항목 3
항목 2
항목 4 항목 3 항목 1
항목 4 항목 5

데이터 포맷

두 열의 데이터가 비슷한 형식인지 확인하는 것이 중요합니다. VLOOKUP은 대소문자를 구분 하므로 대문자와 소문자가 중요합니다.

형식이 일치하지 않으면 최종 결과에서 일치 오류가 발생할 수 있습니다.

오류 위험을 최소화하기 위해 중복 값이나 빈 셀을 제거하는 것도 좋은 생각입니다.

중복 값을 제거하려면:

1. 열을 선택합니다.

2. 상단 리본의 데이터 탭으로 이동합니다.

3. 데이터 도구 섹션에서 중복 제거를 클릭합니다.

버튼이 조금 보기 어려울 수 있습니다. 이 사진이 도움이 될 것입니다:

빈 셀을 제거하려면:

1. 열을 선택합니다.

2. 상단 리본의 홈 탭으로 이동합니다.

3. "찾기 및 선택" 메뉴를 확장합니다.

4. "특별 메뉴로 이동"을 선택합니다.

5. 옵션에서 "공백"을 선택합니다.

6. 셀 섹션에서 "삭제"를 클릭합니다.

VLOOKUP을 사용하여 두 열을 비교하는 방법

데이터가 준비되면 VLOOKUP 수식을 작성하여 Excel에서 두 열을 비교하고 일치 항목을 식별할 수 있습니다. 다음과 같이하세요:

1. 비교 결과를 표시할 새 열에서 셀을 선택합니다(예: 셀 C2).

2. 다음 수식을 입력합니다. =VLOOKUP(A2, B:B, 1, FALSE))

3. Enter 키를 눌러 수식을 적용합니다.

4. 일치하는 값이 있는 경우 결과 열의 동일한 행(예: 열 C)에 값이 표시됩니다.

5. 수식을 C2에서 아래로 드래그하여 필요한 만큼 셀에 복사합니다.

샘플 데이터를 사용하는 경우 비교 결과를 세 번째 열에 입력하세요. 수식은 목록 1의 4개 항목 중 3개의 일치 항목을 찾습니다.

항목 2는 #N/A로 표시됩니다. Excel에서 일치하는 항목이 적용되지 않는다고 알려줍니다. 즉 찾을 수 없습니다.

그것은 정확하고 누락된 값에 대한 유용한 정보입니다. 그러나 일부 Excel 사용자는 일부 데이터 문제 또는 기능 오류가 있다고 생각할 수 있습니다.

일치하는 항목이 없다는 다른 표시를 표시하는 것이 좋습니다. 그것은 단순히 공백 일 수 있습니다.

이렇게 하려면 다음과 같이 VLOOKUP 함수를 IFNA 함수와 결합합니다.

=IFNA(VLOOKUP(A1, B:B, 1, FALSE), "")

IFNA 기능은 #N/A 오류를 감지하고 출력을 빈 공간("")으로 바꿉니다. ISNA 기능을 사용하거나 IF 기능과 함께 추가 논리를 사용할 수도 있습니다.

기타 오류 처리

VLOOKUP은 때때로 비교 결과에서 다른 오류를 생성할 수 있습니다. #REF ! Excel의 오류는 데이터의 진정한 문제입니다.

일반적으로 지정된 범위가 올바르지 않을 때 발생합니다. 이 예에서는 전체 열을 참조했지만 수직 셀 범위를 사용할 수도 있습니다.

참조하는 검색 범위가 비교하려는 모든 값을 포함하는지 확인하십시오.

열 비교를 위한 VLOOKUP의 대안

일치 항목을 찾기 위해 Excel에서 두 열을 비교할 때 두 가지 주요 대체 조회 기능이 있습니다.

1. 인덱스 및 일치 기능 사용

VLOOKUP을 사용하는 대신 INDEX 및 MATCH 함수를 결합하여 Excel에서 두 열을 비교할 수 있습니다.

이 방법은 데이터를 조회하는 보다 유연한 방법을 제공하며 인접하지 않은 열로 작업하거나 열 인덱스가 변경될 수 있는 경우에 특히 유용합니다.

결과 열에 입력하는 구문은 다음과 같습니다.

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

• Return_range : 반환하려는 데이터가 포함된 셀의 범위입니다.

• Lookup_value : 조회 범위 내에서 검색하려는 값입니다.

• Lookup_range : 조회 값을 찾으려는 셀 범위입니다.

이전 예에서 동일한 데이터를 사용하여 VLOOKUP 수식을 다음과 같이 바꿉니다.

=(INDEX(B:B, MATCH(A2, B:B, 0)))

MATCH 함수는 조회 범위 내에서 조회 값의 상대적 위치를 반환하고 INDEX 함수는 반환 범위에서 해당 값을 반환합니다.

동일한 데이터에 대한 결과는 이전에 사용한 VLOOKUP과 동일합니다. 이 그림은 사용 중인 기능을 보여줍니다.

#N/A 오류를 사용자 지정 메시지로 바꿀 수도 있습니다. 다음은 IFERROR 함수를 사용하는 예입니다.

=IFERROR(INDEX(B:B, MATCH(A2, B:B, 0)), "찾을 수 없음")

2. Excel에서 XLOOKUP 사용

Excel 365 또는 Excel 2019 사용자의 경우 XLOOKUP은 두 열에서 공통 값을 찾기 위한 VLOOKUP의 대체 논리 테스트입니다.

XLOOKUP은 몇 가지 이점을 제공합니다. 이를 사용하여 수평 및 수직으로 데이터를 검색하고, 인접하지 않은 열로 작업하고, 오류에 대한 사용자 정의 값을 지정할 수 있습니다.

XLOOKUP 구문은 다음과 같습니다.

=XLOOKUP(lookup_value, lookup_range, return_range, [if_not_found], [match_mode], [search_mode])

• lookup_value : 조회 범위 내에서 검색하려는 값입니다.

• lookup_range : 조회 값을 찾으려는 셀 범위입니다.

• return_range : 반환할 데이터가 포함된 셀의 범위입니다.

• 필요에 따라 사용자 지정 오류 값, 일치 모드 및 검색 모드 매개변수를 추가합니다.

XLOOKUP은 조회 범위 내에서 조회 값을 찾고 반환 범위에서 해당 값을 반환합니다.

이 수식은 여러 열의 데이터 집합에 포함된 두 개의 목록이 있는 경우에 특히 유용합니다. 이전 예에서 목록은 첫 번째 열과 두 번째 열에 있었지만 시트에는 그보다 더 많은 데이터가 있을 수 있습니다.

다음은 스프레드시트의 첫 번째 열과 세 번째 열에 두 개의 목록이 있는 예입니다.

XLOOKUP 수식은 다음과 같습니다.

=XLOOKUP(A2,C:C,C:C,””)

이 그림은 D 열의 비교 결과를 보여줍니다. 첫 번째 값은 있지만 두 번째 값은 없습니다.

추가 오류 수식은 없지만 누락된 값은 공백으로 표시됩니다. 맞춤 오류 값인 네 번째 매개변수를 사용하고 있기 때문입니다. 이 경우 빈 문자열입니다.

보너스로 XLOOKUP을 사용하여 여러 행을 비교하는 방법을 보여드리겠습니다. 행 1과 행 2에 두 개의 목록이 있는 경우 수식을 가장 간단하게 사용하는 방법은 다음과 같습니다.

=XLOOKUP(B1, 2:2, 2:2, "")

이 그림은 두 행의 결과를 보여줍니다.

향상된 기능과 유연성을 갖춘 XLOOKUP은 일치 항목이 있는지 확인하기 위해 Excel에서 목록을 비교하기 위한 VLOOKUP의 탁월한 대안입니다.

VLOOKUP 성능을 향상시키는 5가지 팁

VLOOKUP 기능을 최적화하면 긴 대기 시간을 피하고 Excel 워크시트의 전반적인 응답성을 개선할 수 있습니다.

다음은 Excel에서 VLOOKUP의 성능을 개선하기 위해 적용할 수 있는 6가지 팁과 요령입니다.

1. 조회 범위 제한

예제에서 전체 열을 사용하여 간단하게 만들었습니다. 많은 양의 데이터로 작업하는 경우에는 그렇게 하지 않는 것이 좋습니다. 전체 열을 사용하면 Excel의 계산 프로세스가 느려질 수 있습니다.

대신 데이터에 필요한 정확한 범위를 사용하십시오(예: A1:A100). 이렇게 하면 VLOOKUP 함수가 평가해야 하는 셀 수가 줄어듭니다.

2. 절대 참조 사용

데이터 범위(예: B2에서 B5까지의 셀)를 지정할 때 절대 참조를 사용하십시오. 이렇게 하면 수식이 일관되고 여러 셀에서 수식을 복사할 때 데이터가 존재합니다.

다음은 예입니다.

=VLOOKUP(A2, $B$2:$B$5, 1, FALSE)

3. 데이터 정렬

조회 열의 데이터가 오름차순으로 정렬되어 있다는 것을 알고 있는 경우 'range_lookup' 인수에 대해 TRUE 또는 1과 함께 VLOOKUP을 사용할 수 있습니다.

이렇게 하면 Excel에서 모든 셀에 대한 정확한 일치보다 빠른 근사 일치를 수행하게 됩니다. 그러나 잘못된 정렬은 잘못된 결과를 초래할 수 있으므로 이 옵션을 사용할 때는 주의해야 합니다.

4. 이중 VLOOKUP 사용

두 가지 VLOOKUP 함수를 사용하여 Excel에서 검색 프로세스 속도를 높일 수 있습니다.

첫 번째 VLOOKUP은 'col_index_num'을 1로 설정하고 'range_lookup'을 TRUE로 설정하여 조회 값이 존재하는지 확인합니다.

TRUE를 반환하면 두 번째 VLOOKUP은 'range_lookup'이 TRUE로 설정된 원하는 값을 검색합니다.

5. 조건부 서식 사용

Excel 워크시트에서 조건부 서식을 사용하여 지정된 열에서 일치하거나 누락된 값을 강조 표시할 수 있습니다. 고유한 값에 색상을 적용할 수도 있습니다. 이렇게 하면 데이터를 더 쉽게 읽을 수 있습니다.

홈 탭의 스타일 그룹에서 조건부 서식 메뉴를 찾을 수 있습니다.

VLOOKUP의 고급 사용

대응 표본 T 검정은 두 관련 표본의 평균을 비교하는 데 사용됩니다. 이 비디오는 Excel에서의 사용법을 보여줍니다.

분석 중에 참조해야 하는 추가 데이터나 변수가 있는 경우 VLOOKUP을 사용하여 다른 테이블이나 워크시트에서 필요한 값을 검색할 수 있습니다.

예를 들어 VLOOKUP을 사용하여 데이터 세트의 각 쌍 관찰에 대한 인구 통계 정보 또는 치료 조건을 검색할 수 있습니다.

우리의 마지막 말

지금까지 VLOOKUP 사용에 대해 확실하게 이해했습니다. 이 강력한 기능을 사용하면 두 목록 간의 차이점과 일치하는 값을 빠르게 식별하여 데이터 분석을 보다 효율적이고 정확하게 수행할 수 있습니다.

또한 XLOOKUP, INDEX 및 MATCH와 같은 다른 조회 및 참조 함수의 예도 보았습니다. 이러한 요소를 기술에 추가하면 데이터 분석 기능이 더욱 강화됩니다.

VLOOKUP 기술을 계속 연습하고 다듬으면 곧 Excel에서 열을 비교하는 전문가가 되어 시간을 절약하고 생산성을 높일 수 있습니다.