두 통합 문서 간의 VLOOKUP: 쉬운 단계별 가이드

Excel에서 여러 통합 문서로 작업하는 경우 VLOOKUP 함수를 사용하여 한 통합 문서에서 다른 통합 문서로 데이터를 검색해야 할 수 있습니다.

VLOOKUP 함수는 별도의 통합 문서에 있는 테이블에서 특정 값을 검색하고 동일한 행에서 해당 값을 반환합니다. 구문에는 두 번째 통합 문서 및 워크시트 이름에 대한 정규화된 참조가 포함되어야 합니다.

함수를 처음 사용할 때 올바른 참조를 얻는 것이 어려울 수 있습니다. 이 문서에서는 샘플 스프레드시트를 사용하여 공식을 입력하는 방법을 정확하게 보여줍니다.

또한 VLOOKUP의 몇 가지 향상된 기능과 함수에 대한 몇 가지 대안에 대해 알아봅니다.

시작하자!

목차

• VLOOKUP이란 무엇입니까?
• 두 통합 문서 간에 VLOOKUP을 사용하는 방법
  • 샘플 데이터
  • 1단계: 두 통합 문서를 모두 열어 두기
  • 2단계: VLOOKUP 수식 입력 시작
  • 3단계: 두 번째 통합 문서를 참조하는 방법
• 3가지 일반적인 VLOOKUP 오류 및 해결 방법
  • 1. #N/A 오류
  • 2. #참고! 열 번호 오류
  • 3. 따옴표 및 대괄호 오류
• 다양한 통합 문서를 관리하기 위한 4가지 팁
• 데이터를 자동으로 업데이트하는 방법
• VLOOKUP 함수의 고급 사용
• VLOOKUP의 2가지 주요 대안
  • 1. 인덱스 매치
  • 2. 엑스룩업
• 여러 시트에서 VLOOKUP을 사용하는 방법
  • 1단계: 구성 요소 식별
  • 2단계: 데이터 설정
  • 3단계: VLOOKUP 함수 사용
  • (선택 사항) 4단계: #N/A 오류 문제 해결
• 마지막 생각들

VLOOKUP이란 무엇입니까?

제품 판매 통합 문서와 각 제품의 가격이 포함된 별도의 통합 문서가 있다고 가정합니다. 두 번째 통합 문서의 가격에 액세스하여 첫 번째 통합 문서의 계산에 사용하려고 합니다.

VLOOKUP은 이 작업을 위한 하나의 솔루션입니다. 이 용어는 "수직 조회"를 의미합니다 . “

열에서 특정 값을 검색하고 위치에 따라 다른 열에서 해당 값을 반환할 수 있는 내장 함수입니다.

다음과 함께 사용할 수 있습니다.

• 단일 스프레드시트

• 동일한 통합 문서의 여러 시트

• 두 개의 Excel 통합 문서

여러 통합 문서의 추가 복잡성이 없는 경우 VLOOKUP을 사용하여 Excel에서 두 열을 비교하는 방법 에 대한 이 자습서를 확인하세요 .

두 통합 문서 간에 VLOOKUP을 사용하는 방법

VLOOKUP 기능은 여러 워크시트 또는 통합 문서에서 데이터를 검색해야 할 때 특히 유용합니다.

이를 위한 세 가지 주요 단계는 다음과 같습니다.

1. 두 통합 문서를 모두 엽니다.

2. 첫 번째 Excel 통합 문서에 VLOOKUP 수식을 입력합니다.

3. 두 번째 통합 문서에 대한 완전한 참조를 제공하십시오.

샘플 데이터

이 간단한 자습서는 샘플 데이터를 기반으로 하는 예제를 통해 작동합니다. 계속 진행하려면 "Sales"라는 새 통합 문서를 만들고 이 데이터를 A열과 B열에 입력합니다.

• 사과, 40

• 오렌지, 30

• 배, 70

• 바나나, 20

가격 정보를 보관할 세 번째 열을 추가합니다.

"제품"이라는 두 번째 Excel 통합 문서를 만들고 Sheet1의 이름을 "가격"으로 바꿉니다. A 열과 B 열에 이 데이터를 입력합니다.

• 사과, 1.50

• 오렌지, 2.30

• 배, 3.60

• 바나나, 2.10

데이터는 다음과 같습니다.

첫 번째 스프레드시트에서 판매 수익을 계산하려면 두 번째 스프레드시트에서 가격을 조회해야 합니다. 구조에 VLOOKUP!

1단계: 두 통합 문서를 모두 열어 두기

두 통합 문서 간에 VLOOKUP 수식을 사용하는 경우 두 통합 문서가 모두 열려 있어야 합니다. 두 번째 통합 문서가 닫히면 함수는 오류를 반환합니다.

별도로 열거나 Excel에서 "열기" 명령을 사용하여 두 개를 한 번에 열 수 있습니다. 두 가지 방법으로 통합 문서를 열 수 있습니다.

• 파일 탭으로 이동하여 "열기"를 선택합니다.

• "Ctrl + O" 키보드 단축키를 사용하십시오 .

두 통합 문서가 모두 열리면 Excel 창 하단에 있는 해당 탭을 클릭하여 두 통합 문서 간에 전환할 수 있습니다.

2단계: VLOOKUP 수식 입력 시작

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

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

• lookup_value : 다른 통합 문서에서 조회하려는 값입니다.

• table_array : 검색하려는 데이터가 포함된 다른 통합 문서의 셀 범위입니다.

• col_index_num : 검색하려는 데이터가 포함된 범위의 열 번호입니다.

• range_lookup : 이 마지막 매개변수는 선택 사항입니다. TRUE로 설정하거나 생략하면 VLOOKUP에서 대략적인 일치 항목을 찾습니다. FALSE로 설정하면 VLOOKUP은 정확히 일치하는 항목만 찾습니다.

샘플 데이터의 두 번째 통합 문서에서 사과 가격을 조회한다고 가정해 보겠습니다. 공식은 다음과 같이 분해됩니다.

• lookup_value는 첫 번째 통합 문서의 A2 셀에 있습니다.

• 테이블 배열은 두 번째 통합 문서의 A1:B5 셀 범위에 있습니다.

• col_index_num은 가격을 보유하는 두 번째 열입니다.

VLOOKUP 수식에서 가장 복잡한 부분은 두 번째 통합 문서를 올바르게 참조하는 것입니다.

3단계: 두 번째 통합 문서를 참조하는 방법

두 개의 통합 문서에 대한 VLOOKUP 수식을 완성하려면 두 번째 통합 문서의 다음 세부 정보를 포함하는 정규화된 참조를 사용해야 합니다.

• 통합 문서 이름

• 시트 이름

• 셀 범위

이렇게 하면 Excel에서 찾고 있는 데이터를 찾을 위치를 정확히 알 수 있습니다. 정규화된 참조는 VLOOKUP 함수의 table_array 인수에 사용됩니다.

샘플 데이터의 값은 다음과 같습니다.

• 통합 문서 이름: Product.xlsx

• 시트 이름: 가격

• 셀 범위: $A1:$B5

첫 번째 항목의 가격을 조회하려면 첫 번째 통합 문서의 C2 셀에 다음 수식을 입력합니다.

=VLOOKUP(A2, [제품.xlsx]가격!$A$1:$B$5, 2, FALSE)

이 그림은 첫 번째 셀의 수식 입력줄에서 실행 중인 함수를 보여줍니다.

통합 문서 이름은 대괄호로 묶여 있고 시트 이름 뒤에는 느낌표가 붙습니다.

Excel 파일 이름에 공백이나 특수 문자가 포함된 경우 전체 통합 문서와 시트를 작은따옴표로 묶어야 합니다.

수식은 셀 범위($ 접두사)에 절대값을 사용합니다. 이렇게 하면 채우기 핸들을 클릭하여 수식을 나머지 행에 복사할 수 있습니다.

VLOOKUP 결과는 다음과 같습니다.

그리고 그게 다야! 이제 VLOOKUP을 효과적으로 사용하는 방법을 알게 되었습니다. 오류가 발생할 경우 도움이 되도록 다음 섹션에서 가장 일반적인 VLOOKUP 오류 세 가지를 컴파일했습니다.

3가지 일반적인 VLOOKUP 오류 및 해결 방법

이제 다른 통합 문서에서 VLOOKUP을 사용하는 방법을 배웠으므로 몇 가지 일반적인 오류가 발생할 수 있습니다.

다음은 가장 자주 발생하는 세 가지 오류와 해결 방법입니다.

1. #N/A 오류

테이블 배열에서 조회 값을 찾을 수 없는 경우 #N/A 오류가 발생합니다. 이 오류를 수정하려면 다음을 시도해 보십시오.

• 조회 값의 철자를 확인하십시오.

• 조회 값이 테이블 배열의 첫 번째 열에 있는지 확인하십시오.

• 조회 값이 두 시트에서 텍스트로 서식이 지정되지 않았는지 확인합니다.

• 정확히 일치하는 항목을 찾으려면 range_lookup 인수가 FALSE로 설정되어 있는지 확인하십시오.

위의 예는 두 번째 통합 문서에서 체리에 대해 일치하는 값을 찾는 데 실패했습니다.

2. #참고! 열 번호 오류

# 심판! col_index_num 인수가 테이블 배열의 열 수보다 크면 오류가 발생합니다.

이 오류를 수정하려면 col_index_num 인수가 소스 테이블의 열 수보다 작거나 같은지 확인하십시오.

아래 그림에서 col_index_num은 3으로 설정되어 있지만 참조 범위에는 두 개의 열만 있습니다.

3. 따옴표 및 대괄호 오류

따옴표 및 대괄호 오류는 따옴표 또는 대괄호가 포함된 값으로 VLOOKUP을 수행할 때 발생합니다.

이 오류를 수정하려면 다음을 시도해 보십시오.

• 조회 값 주위에 큰따옴표를 사용하십시오.

• 큰따옴표를 삽입하려면 CHAR(34) 함수를 사용하십시오 .

• 조회 값에 따옴표나 대괄호가 포함된 경우 IFERROR 함수를 사용하여 빈 셀을 반환합니다.

이러한 오류에 익숙해지면 오류를 수정하는 것이 간단합니다! VLOOKUP 전문가가 되는 데 도움이 되도록 다음 섹션에서 다양한 통합 문서를 관리하기 위한 네 가지 팁을 제공했습니다.

다양한 통합 문서를 관리하기 위한 4가지 팁

여러 통합 문서로 작업할 때 어떤 통합 문서에 어떤 데이터가 포함되어 있는지 추적하는 것이 중요합니다.

온전한 정신을 유지하기 위한 4가지 최고의 팁은 다음과 같습니다.

1. 이름 지정 규칙을 사용하여 어떤 Excel 스프레드시트에 어떤 데이터가 포함되어 있는지 기억할 수 있습니다.

2. 수식에 절대 참조를 사용하여 수식을 다른 위치나 Excel 시트로 이동하거나 복사하는 경우에도 조회 범위가 올바른 셀을 참조하도록 합니다.

3. 소스 데이터에서 조회 값을 찾을 수 없는 경우 발생할 수 있는 #N/A 오류를 처리하려면 IFERROR 함수를 사용하십시오.

4. 덮어쓸 수 없도록 참조 통합 문서의 셀을 잠그 십시오 .

데이터를 자동으로 업데이트하는 방법

여러 Excel 파일로 작업할 때 데이터를 수동으로 업데이트 하려면 시간이 많이 걸릴 수 있습니다 .

다른 통합 문서에서 데이터를 추가하거나 제거할 때 VLOOKUP 수식이 자동으로 업데이트되도록 하려면 INDIRECT 함수를 사용할 수 있습니다.

방법은 다음과 같습니다.

• VLOOKUP 수식에서 테이블 배열 인수를 INDIRECT 함수로 바꿉니다.

• INDIRECT 함수에서 다른 통합 문서의 테이블 배열이 포함된 셀에 대한 참조를 사용합니다.

샘플 통합 문서로 이를 달성하려면 다음 단계를 따르십시오.

1. D 열의 한 셀을 사용하여 "제품" 통합 문서의 "가격" 시트에 대한 참조와 셀 범위 A1:B5를 다음과 같이 입력합니다. [제품.xlsx]가격!A:B

2. "Sales" 통합 문서의 셀 C2에서 다음 수식을 사용합니다. =VLOOKUP(A2, INDIRECT($D$1), 2, FALSE)

3. 수식을 아래로 복사합니다(절대 참조는 스프레드시트가 있는 셀이 보존되도록 합니다).

이러한 작업을 통해 원본 시트의 변경 사항이 연결된 셀에서 자동으로 업데이트됩니다.

이 그림은 VLOOKUP과 결합된 INDIRECT 함수를 보여줍니다.

이 예에서는 "A:B" 범위를 사용하여 두 열을 참조합니다. 즉, 더 많은 제품과 가격을 추가하면 수식이 현재 통합 문서에서 계속 작동합니다.

이제 VLOOKUP의 기능을 맛보았으니 섹션에서 기능의 고급 사용법을 살펴보겠습니다.

VLOOKUP 함수의 고급 사용

이 문서의 예제는 각 조회 값에 대해 단일 일치 항목을 반환합니다. 다른 통합 문서에서 여러 일치 항목으로 작업하려는 경우 배열 수식을 대신 사용할 수 있습니다.

VLOOKUP을 다른 기능과 결합할 수도 있습니다. 예를 들어 복잡한 추가 분석을 위해 IF 함수 내에 포함할 수 있습니다.

복잡한 분석에는 카이 제곱 검정 및 쌍 표본 T 검정과 같은 기술이 포함됩니다. 다음은 Excel에서 카이제곱 검정을 사용하는 예입니다.

고급 사용자는 VLOOKUP을 활용하여 데이터 세트를 비교하고 불일치를 식별합니다. 이 문서에서 Excel에서 불일치를 찾는 다른 방법을 찾을 수 있습니다 .

하지만 어떤 이유로든 VLOOKUP을 사용하고 싶지 않다면 어떻게 해야 할까요? 당신은 운이 좋다! 다음 섹션에서 다룰 몇 가지 대안이 있습니다.

VLOOKUP의 2가지 주요 대안

다른 시트에서 값을 조회할 때 VLOOKUP이 자주 사용되는 기능입니다. 그러나 특정 상황에서 유용할 수 있는 몇 가지 대안이 있습니다.

가장 일반적인 두 가지는 다음과 같습니다.

• 인덱스 매치

• XLOOKUP

1. 인덱스 매치

INDEX 및 MATCH는 테이블에서 값을 조회하는 데 사용할 수 있는 두 함수의 강력한 조합입니다. 조회 값이 테이블의 가장 왼쪽 열에 있어야 하는 VLOOKUP과 달리 INDEX MATCH는 모든 열에서 값을 조회할 수 있습니다.

작동 방식은 다음과 같습니다.

1. INDEX 함수는 행 및 열 번호를 기준으로 지정된 범위의 값을 반환합니다.

2. MATCH 함수는 지정된 범위에서 조회 값의 위치를 ​​반환합니다.

INDEX MATCH를 사용하기 위해서는 검색하고자 하는 범위, 조회 값을 찾고자 하는 행, 결과를 반환하고자 하는 열을 지정해야 합니다.

다음은 예제 수식입니다.

=INDEX(테이블 범위,MATCH(조회값,조회 범위,0),열 인덱스)

이것을 샘플 스프레드시트와 함께 사용하기 위한 공식은 다음과 같습니다.

=INDEX([제품.xlsx]가격!$B$1:$B$5, MATCH(A2, [제품.xlsx]가격!$A$1:$A$5, 0))

이것은 다음과 같이 분해됩니다.

• MATCH 함수는 "제품" 통합 문서의 "가격" 시트에서 A1:A5 범위 내에서 셀 A2(제품 이름)의 값을 검색합니다.

• INDEX 함수는 MATCH 함수에서 제공한 행 번호를 기준으로 "제품" 통합 문서의 "가격" 시트에 있는 B1:B5 범위의 값을 반환합니다.

이 그림은 작동 중인 공식을 보여줍니다.

2. 엑스룩업

XLOOKUP은 Excel 365 에 도입된 최신 기능입니다 . VLOOKUP과 비슷하지만 몇 가지 추가 기능이 있습니다.

XLOOKUP의 가장 큰 장점 중 하나는 조회 열의 왼쪽에 있는 값을 조회할 수 있다는 것입니다.

작동 방식은 다음과 같습니다.

1. XLOOKUP 함수는 조회 값을 기반으로 지정된 범위의 값을 반환합니다.

2. 정확한 일치 또는 대략적인 일치를 원하는지 여부를 지정할 수 있습니다.

3. 일치하는 항목이 없는 경우 반환할 항목을 지정할 수도 있습니다.

XLOOKUP을 사용하려면 조회 값, 검색할 범위, 결과를 반환할 열을 지정해야 합니다.

다음은 예제 수식입니다.

=XLOOKUP(LookupValue,LookupRange,ReturnRange,"찾을 수 없음",0)

이것을 샘플 스프레드시트와 함께 사용하기 위한 공식은 다음과 같습니다.

=XLOOKUP(A2, [제품.xlsx]가격!$A$1:$A$5, [제품.xlsx]가격!$B$1:$B$5)

이것은 다음과 같이 분해됩니다.

• A2: 조회하려는 값(제품 이름)입니다.

• [Product.xlsx]Prices!$A$1:$A$5: 조회 값을 검색하려는 범위("제품" 통합 문서의 "가격" 시트에 있는 제품 이름).

• [Product.xlsx]Prices!$B$1:$B$5: 반환 값("제품" 통합 문서의 "가격" 시트에 있는 가격)을 포함하는 범위입니다.

이것은 작동하는 공식입니다.

여러 시트에서 VLOOKUP을 사용하는 방법

두 통합 문서 간에 VLOOKUP을 수행해야 하는 경우 여러 시트에서 VLOOKUP을 사용해야 할 수도 있습니다.

다행스럽게도 이를 수행하는 프로세스는 두 통합 문서 간에 VLOOKUP을 수행하는 것과 매우 유사합니다.

네 단계가 있습니다.

1단계: 구성 요소 식별

하나의 시트에 대해 테이블 ​​배열을 포함하는 대신 시트 이름도 포함해야 합니다.

예를 들어 Sales_reports.xlsx 통합 문서의 Jan 시트에서 A2:B6 범위의 값을 검색하려는 경우 수식은 다음과 같습니다.

=VLOOKUP(A2,[Sales_reports.xlsx]1월!$A$2:$B$6,2,FALSE)

2단계: 데이터 설정

데이터가 여러 시트에서 쉽게 참조할 수 있는 방식으로 구성되어 있는지 확인하십시오.

특정 참조 데이터에 대해 다른 탭을 만드는 것을 고려할 수 있습니다.

3단계: VLOOKUP 함수 사용

데이터가 설정되면 VLOOKUP 기능을 사용하여 다른 시트에서 데이터를 검색하고 가져올 수 있습니다.

위에 표시된 대로 수식에 시트 이름을 포함해야 합니다.

(선택 사항) 4단계: #N/A 오류 문제 해결

여러 시트에서 VLOOKUP을 사용할 때 #N/A 오류가 발생하면 수식을 다시 확인하여 필요한 모든 시트와 범위를 올바르게 참조했는지 확인하세요.

범위 조회가 아닌 정확한 일치를 수행하고 있는지 확인할 수도 있습니다.

마지막 생각들

VLOOKUP을 사용하여 두 스프레드시트에서 데이터를 검색하는 방법을 배웠습니다. 이 다목적 기능을 사용하면 다른 통합 문서의 해당 값을 기반으로 한 통합 문서의 값을 쉽게 조회할 수 있습니다.

VLOOKUP 기능의 기능은 단일 워크시트를 훨씬 넘어 다양한 통합 문서에 도달하여 데이터를 연결하고 분석합니다.

이 기능을 마스터하는 것은 데이터를 관리하고 해석할 수 있는 새로운 가능성을 열어주기 때문에 Excel에 능숙해지기 위한 중요한 단계입니다.

INDIRECT, INDEX-MATCH 또는 XLOOKUP과 같은 기능을 통합하여 데이터 조회의 유연성과 효율성을 더욱 향상시킬 수 있습니다.

Excel의 방대한 기능을 계속 연습하고 탐색하십시오. 이것은 빙산의 일각에 불과합니다. 즐거운 데이터 헌팅!