엑셀 VLOOKUP

엑셀 VLOOKUP 기능

VLOOKUP이란?

 

Excel 함수이며 지정한 값을 검색하고 다른 열에서 일치하는 값을 반환합니다. 좀 더 기술적으로 VLOOKUP 함수는 지정된 범위의 첫 번째 열에서 값을 조회하고 다른 열에서 같은 행의 값을 반환합니다.

 

일반적으로 Excel VLOOKUP은 고유 식별자를 기반으로 데이터 집합을 검색하여 해당 고유 식별자와 연결된 정보를 제공합니다.

문자 "V"는 "vertical"을 나타내며 열이 아닌 행에서 값을 조회하는 HLOOKUP 함수와 VLOOKUP을 구분하는 데 사용됩니다(H는 "horizontal"을 나타냄).

 

이 함수는 Excel 365부터 Excel 2007까지 모든 버전에서 사용할 수 있습니다.

 

Excel 365 및 Excel 2021에서는 VLOOKUP의 보다 유연하고 강력한 후속 기능인 XLOOKUP 함수를 사용할 수 있습니다.

 

VLOOKUP 구문

VLOOKUP 함수의 구문은 다음과 같습니다.

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

• Lookup_value(필수) - 검색할 값입니다.
  이는 값(숫자, 날짜 또는 텍스트), 셀 참조(조회 값이 포함된 셀에 대한 참조) 또는 다른 함수에서 반환된 값일 수 있습니다. 숫자 및 셀 참조와 달리 텍스트 값은 항상 "큰따옴표"로 묶어야 합니다.
• Table_array(필수) - 조회 값을 검색할 셀과 일치 항목을 검색할 셀의 범위입니다. VLOOKUP 함수는 항상 다양한 텍스트 값, 숫자, 날짜 및 논리값을 포함할 수 있는 table형 배열의 첫 번째 열을 검색합니다.
• Col_index_num (필수) - 값을 반환할 열의 번호입니다. 계산은 table형 배열의 맨 왼쪽 열(1)부터 시작합니다.
• Range_lookup(선택 사항) - 근사 일치 또는 정확한 일치를 검색할지 여부를 결정합니다.
  • TRUE 또는 생략(기본값) - 근사치 일치. 정확히 일치하는 항목이 없으면 수식은 조회 값보다 작은 가장 큰 값을 검색합니다. 조회 열을 오름차순으로 정렬해야 합니다.
  • FALSE - 정확히 일치합니다. 수식은 조회 값과 정확히 동일한 값을 검색합니다. 정확히 일치하는 항목을 찾을 수 없으면 #N/A 값이 반환됩니다.

 

기본 VLOOKUP 수식

다음은 가장 간단한 형식의 Excel VLOOKUP 수식의 예입니다. 

=VLOOKUP("lion", A2:B11, 2, FALSE)

• 1번째 인수(lookup_value)는 수식이 "lion"이라는 단어를 찾는다는 것을 명확하게 나타냅니다.
• 2번째 인수(table_array)는 A2:B11입니다. 검색이 가장 왼쪽 열에서 수행된다는 점을 염두에 두고 위의 공식을 조금 더 읽을 수 있습니다: A2:A11 범위에서 "사자"를 검색합니다. 여태까지는 그런대로 잘됐죠?
• 3번째 인수 col_index_num는 2입니다. 즉, table형 배열에서 두 번째인 B 열에서 일치하는 값을 반환하려고 합니다.
• 4번째 인수 range_lookup는 FALSE로, 정확히 일치하는 항목을 찾고 있음을 나타냅니다.

모든 인수가 설정되면 전체 수식을 읽는 데 아무런 문제가 없습니다 : A2 : A11에서 "lion"을 검색하고, 정확히 일치하는 항목을 찾고, 같은 행의 B 열에서 값을 반환합니다.

 

편의를 위해 E1과 같은 일부 셀에 관심 있는 값을 입력하고 "하드코딩된" 텍스트를 셀 참조로 바꾸고 수식을 가져와 E1에 입력한 값을 조회할 수 있습니다.

=VLOOKUP(E1, A2:B11, 2, FALSE)

 

 

반응형

Excel에서 Vlookup을 수행하는 방법

실제 워크시트에서 VLOOKUP 수식을 사용할 때 기본 경험 법칙은 수식을 다른 셀에 복사할 때 변경되지 않도록 절대 셀 참조(예: $A$2:$C$11)로 테이블 배열을 잠그는 것입니다.

대부분의 경우 조회 값은 상대 참조(예: E2)이거나 열 좌표($E 2)만 잠글 수 있습니다. 수식이 열 아래로 복사되면 참조가 각 행에 대해 자동으로 조정됩니다.

실제로 어떻게 작동하는지 보려면 다음 예를 고려하십시오. 샘플 테이블에 속도(열 A)에 따라 동물의 순위를 매기는 열을 하나 더 추가했으며 세계에서 1번째, 5번째 및 10번째로 빠른 단거리 선수를 찾고자 합니다. 이를 위해 일부 셀에 조회 순위를 입력하고(아래 스크린샷의 E2:E4) 다음 수식을 사용합니다.

 

B 열에서 동물 이름을 가져오려면:

=VLOOKUP($E2, $A$2:$C$11, 2, FALSE)

 

C 열에서 속도를 추출하려면:

=VLOOKUP($E2, $A$2:$C$11, 3, FALSE)

 

F2 및 G2 셀에 위의 수식을 입력하고 해당 셀을 선택한 다음 수식을 아래 행으로 끕니다.

아래쪽 행에 있는 수식을 조사해 보면, 조회 값 참조가 해당 특정 행에 대해 조정된 반면 table형 배열은 변경되지 않은 것을 알 수 있습니다.

 

엑셀 VLOOKUP - 기억해야 할 5 가지!

1. VLOOKUP 함수는 왼쪽을 볼 수 없습니다. 항상 table형 배열의 맨 왼쪽 열에서 검색하고 오른쪽 열에서 값을 반환합니다. 왼쪽에서 값을 가져와야하는 경우 조회 및 반환 열의 위치를 신경 쓰지 않는 INDEX MATCH (또는 Excel 365의 INDEX XMATCH) 조합을 사용하십시오.
2. VLOOKUP 함수는 대/소문자를 구분하지 않으므로 대문자와 소문자가 동일하게 처리됩니다. 대/소문자를 구분하려면 대/소문자를 구분하는 VLOOKUP 수식을 사용합니다.
3. 마지막 매개 변수의 중요성을 기억하십시오. 근사 일치에는 TRUE를 사용하고 정확히 일치하려면 FALSE를 사용합니다.
4. 대략적인 일치 항목을 검색할 때 조회 열의 데이터가 오름차순으로 정렬되어 있는지 확인합니다.
5. 조회 값을 찾을 수 없으면 #N/A 오류가 반환됩니다. 

 

Excel VLOOKUP 예제

 

Excel의 다른 시트에서 Vlookup하는 방법

실제로 Excel VLOOKUP 함수는 동일한 워크시트의 데이터에 거의 사용되지 않습니다. 대부분의 경우 다른 워크시트에서 일치하는 데이터를 가져와야 합니다.

다른 Excel 시트에서 Vlookup하려면 워크시트 이름 뒤에 느낌표를 table_array 인수에서 범위 참조 앞에 입력합니다. 예를 들어 Sheet2에서 A10:B2 범위에서 검색하려면 다음 수식을 사용합니다.

=VLOOKUP("Product1", Sheet2!A2:B10, 2)

 

물론 시트 이름을 수동으로 입력할 필요는 없습니다. 수식 입력을 시작하고 table_array 인수와 관련하여 조회 워크시트로 전환하고 마우스를 사용하여 범위를 선택하기만 하면 됩니다.

 

예를 들어 가격 워크시트에서 A2:A2 범위의 A9 값을 조회하고 C 열에서 일치하는 값을 반환하는 방법은 다음과 같습니다.

=VLOOKUP(A2, Prices!$A$2:$C$9, 3, FALSE)

• 스프레드시트 이름에 공백이나 알파벳이 아닌 문자가 포함된 경우 작은따옴표로 묶어야 합니다(예: 'Price list'!$A$2:$C$9).
• 여러 셀에 VLOOKUP 수식을 사용하는 경우 $A$2:$C$9와 같이 $ 기호로 table_array 잠그는 것을 잊지 마십시오.

 

반응형

Excel의 다른 통합 문서에서 Vlookup하는 방법

다른 Excel 통합 문서에서 Vlookup하려면 워크시트 이름 앞에 대괄호로 묶인 통합 문서 이름을 입력합니다.

예를 들어 Price_List.xlsx 통합 문서의 Price라는 시트에서 A2 값을 조회하는 수식은 다음과 같습니다.

=VLOOKUP(A2, [Price_List.xlsx]Prices!$A$2:$C$9, 3, FALSE)

 

통합 문서 이름이나 워크시트 이름에 공백이나 알파벳이 아닌 문자가 포함된 경우 다음과 같이 작은따옴표로 묶어야 합니다.

=VLOOKUP(A2, '[Price List.xlsx]Prices'!$A$2:$C$9, 3, FALSE)

다른 통합 문서를 참조하는 VLOOKUP 수식을 만드는 가장 쉬운 방법은 다음과 같습니다.

1. 두 파일을 모두 엽니다.
2. 수식 입력을 시작하고, 다른 통합 문서로 전환하고, 마우스를 사용하여 table형 배열을 선택합니다.
3. 나머지 인수를 입력하고 Enter 키를 눌러 수식을 완성합니다.

결과는 아래 스크린 샷과 다소 비슷합니다.

조회 테이블이 있는 파일을 닫으면 VLOOKUP 수식이 계속 작동하지만 이제 닫힌 통합 문서의 전체 경로가 표시됩니다.

 

다른 시트의 명명 된 범위에서 Vlookup하는 방법

여러 수식에서 동일한 조회 범위를 사용하려는 경우 명명된 범위를 만들고 table_array 인수에 직접 이름을 입력할 수 있습니다.

명명된 범위를 만들려면 셀을 선택하고 수식 입력줄 왼쪽에 있는 이름 상자에 원하는 이름을 입력하기만 하면 됩니다. 

 

이 예에서는 조회 시트의 데이터 셀(A2020:C2)에 Prices_9라는 이름을 지정하고 다음과 같은 간단한 수식을 얻습니다.

=VLOOKUP(A2, Prices_2020, 3, FALSE)

 

Excel의 대부분의 이름은 전체 통합 문서에 적용되므로 명명된 범위를 사용할 때 워크시트 이름을 지정할 필요가 없습니다.

명명된 범위가 다른 통합 문서에 있는 경우 다음과 같이 범위 이름 앞에 통합 문서의 이름을 입력합니다.

=VLOOKUP(A2, 'Price List.xlsx'!Prices_2020, 3, FALSE)

 

명명된 범위는 변경되지 않으므로 수식을 이동하거나 복사하는 위치에 관계없이 table형 배열이 잠긴 상태로 유지되도록 할 수 있습니다.

조회 범위를 완전한 기능의 Excel 테이블로 변환 한 경우 테이블 이름 (예 : 아래 수식의 Price_table)을 기반으로 Vlookup을 수행 할 수 있습니다.

=VLOOKUP(A2, Price_table, 3, FALSE)

 

구조적 참조라고도 하는 테이블 참조는 복원력이 있으며 많은 데이터 조작에 영향을 받지 않습니다. 예를 들어 참조 업데이트에 대한 걱정 없이 조회 테이블에 새 행을 제거하거나 추가할 수 있습니다.

 

반응형

VLOOKUP 수식에서 와일드 카드 사용

다른 많은 수식과 마찬가지로 Excel VLOOKUP 함수에는 다음과 같은 와일드카드 문자를 사용할 수 있습니다.

• 단일 문자와 일치하는 물음표(?)
• 별표(*)는 모든 문자 시퀀스와 일치합니다.

와일드카드는 다음과 같은 많은 상황에서 매우 유용합니다.

• 찾고 있는 정확한 텍스트가 기억나지 않을 때.
• 셀 내용의 일부인 텍스트 문자열을 찾는 경우.
• 조회 열에 선행 또는 후행 공백이 포함된 경우 이 경우 정상적인 공식이 작동하지 않는 이유를 알아내려고 머리를 쥐어뜯을 수 있습니다.

 

예 1. 특정 문자로 시작하거나 끝나는 텍스트 조회

아래 데이터베이스에서 특정 고객을 찾고 싶다고 가정해 보겠습니다. 성은 기억나지 않지만 "ack"로 시작한다고 확신합니다.

A 열의 성을 반환하려면 다음 Vlookup 와일드카드 수식을 사용합니다.

=VLOOKUP("ack*", $A$2:$B$10, 1, FALSE)

열 B에서 라이센스 키를 검색하려면 다음을 사용하십시오(차이점은 열 인덱스 번호에만 있음).

=VLOOKUP("ack*", $A$2:$B$10, 2, FALSE)

일부 셀(예: E1)에 이름의 알려진 부분을 입력하고 와일드카드 문자를 셀 참조와 결합할 수도 있습니다.

=VLOOKUP(E1&"*", $A$2:$B$10, 1, FALSE)

아래 스크린 샷은 결과를 보여줍니다.

다음은 와일드카드가 있는 몇 가지 VLOOKUP 수식입니다.

"son"으로 끝나는 성을 찾으십시오.

=VLOOKUP("*son", $A$2:$B$10, 1, FALSE)

"joh"로 시작하고 "son"으로 끝나는 이름을 가져옵니다.

=VLOOKUP("joh*son", $A$2:$B$10, 1, FALSE)

5자 성을 가져옵니다.

=VLOOKUP("?????", $A$2:$B$10, 1, FALSE)

 

예 2. 셀 값을 기반으로 하는 VLOOKUP 와일드카드

앞의 예에서 앰퍼샌드(&)와 셀 참조를 연결하여 조회 문자열을 만들 수 있다는 것을 이미 알고 있습니다. 어떤 위치에서든 주어진 문자를 포함하는 값을 찾으려면 셀 참조 앞뒤에 앰퍼샌드를 배치합니다.

특정 라이센스 키에 해당하는 이름을 얻고 싶지만 전체 키를 모르고 몇 문자만 알고 있다고 가정해 보겠습니다. A열의 키, B열의 이름, E1의 대상 키 일부를 사용하여 다음과 같은 방식으로 와일드카드 Vlookup을 수행할 수 있습니다.

키를 추출합니다.

=VLOOKUP("*"&E1&"*", $A$2:$B$10, 1, FALSE)

 

이름을 추출합니다.

=VLOOKUP("*"&E1&"*", $A$2:$B$10, 2, FALSE)

• 와일드카드 VLOOKUP 수식이 제대로 작동하려면 정확히 일치(FALSE가 마지막 인수임)를 사용합니다.
• 일치하는 항목이 두 개 이상 발견되면 첫 번째 항목이 반환됩니다.

 

반응형

VLOOKUP TRUE 대 FALSE

이제 Excel VLOOKUP 함수의 마지막 인수를 자세히 살펴볼 차례입니다. 선택 사항이지만 range_lookup 매개 변수는 매우 중요합니다. TRUE를 선택했는지 또는 FALSE를 선택했는지에 따라 수식의 결과가 달라질 수 있습니다.

 

Excel VLOOKUP 정확히 일치(FALSE)

range_lookup FALSE로 설정된 경우 Vlookup 수식은 조회 값과 정확히 같은 값을 검색합니다. 두 개 이상의 일치 항목이 발견되면 첫 번째 일치 항목이 반환됩니다. 정확히 일치하는 항목을 찾을 수 없으면 #N/A 오류가 발생합니다.

 

Excel VLOOKUP 근사 일치(TRUE)

range_lookup TRUE로 설정되거나 생략(기본값)된 경우 수식은 가장 근접한 일치 항목을 찾습니다. 보다 정확하게는 정확히 일치하는 항목을 먼저 검색하고, 정확히 일치하는 항목을 찾을 수 없는 경우 조회 값보다 작은 다음으로 큰 값을 찾습니다.

 

대략적인 일치 Vlookup은 다음과 같은 주의 사항과 함께 작동합니다.

• 조회 열은 가장 작은 것부터 가장 큰 것까지 오름차순으로 정렬해야 하며, 그렇지 않으면 올바른 값을 찾지 못할 수 있습니다.
• 조회 값이 조회 배열에서 가장 작은 값보다 작으면 #N/A 오류가 반환됩니다.

다음 예는 정확히 일치와 근사 일치 Vlookup의 차이점과 각 수식을 사용하는 것이 가장 좋은 시기를 이해하는 데 도움이 됩니다.

 

예 1. 정확한 일치 Vlookup을 수행하는 방법

정확히 일치하는 항목을 찾으려면 마지막 인수에 FALSE를 입력하기만 하면 됩니다.

이 예에서는 동물 속도 테이블을 가져와서 열을 바꾸고 시속 80, 50, 30마일을 달릴 수 있는 동물을 찾아 보겠습니다. D2, D3 및 D4의 조회 값을 사용하여 E2에 아래 수식을 입력 한 다음 두 개의 셀에 복사합니다.

=VLOOKUP(D2, $A$2:$B$12, 2, FALSE)

 

여기에서 볼 수 있듯이 수식은 시간당 정확히 3개를 실행하기 때문에 E50에서 "Lion"을 반환합니다. 다른 두 조회 값의 경우 정확히 일치하는 항목을 찾을 수 없으며 #N/A 오류가 나타납니다.

 

 

예 2. 대략적인 일치를 Vlookup하는 방법

대략적인 일치 항목을 찾으려면 다음 두 가지 필수 작업을 수행해야 합니다.

• table_array의 첫 번째 열을 가장 작은 열에서 가장 큰 열로 정렬합니다.
• range_lookup 인수에 TRUE를 사용하거나 생략합니다.

조회 열을 정렬하는 것은 VLOOKUP 함수가 조회 값보다 작은 근접 일치 항목을 찾는 즉시 검색을 중지하기 때문에 매우 중요합니다. 데이터가 제대로 정렬되지 않으면 정말 이상한 결과나 #N/A 오류가 발생할 수 있습니다.

샘플 데이터의 경우 대략적인 일치 Vlookup 수식은 다음과 같습니다.

=VLOOKUP(D2, $A$2:$B$12, 2, TRUE)

 

그리고 다음 결과를 반환합니다.

• 조회 값이 "80"인 경우 속도(70)가 조회 값보다 작고 가장 근접한 일치 항목이므로 "Cheetah"가 반환됩니다.
• 조회 값이 "50"이면 정확히 일치하는 항목(Lion)이 반환됩니다.
• 조회 값이 "30"인 경우 조회 값이 조회 열의 가장 작은 값보다 작기 때문에 #N/A 오류가 반환됩니다.

 

반응형

Excel에서 Vlookup을 수행하는 특수 도구

의심할 여지 없이 VLOOKUP은 가장 강력하고 유용한 Excel 함수 중 하나이지만 가장 혼란스러운 기능 중 하나이기도 합니다. 

 

VLOOKUP 마법사 - 복잡한 수식을 쉽게 작성할 수 있는 방법

대화형 VLOOKUP 마법사는 구성 옵션을 안내하여 지정한 조건에 대한 완벽한 수식을 작성합니다. 데이터 구조에 따라 표준 VLOOKUP 함수 또는 왼쪽에서 값을 가져올 수 있는 INDEX MATCH 수식을 사용합니다.

 

맞춤형 공식을 얻으려면 다음을 수행해야 합니다.

1. VLOOKUP 마법사를 실행합니다.

1. 기본 테이블과 조회 테이블을 선택합니다.
2. 다음 열을 지정합니다(대부분의 경우 자동으로 선택됨).
   • 키 열 - 조회할 값을 포함하는 기본 테이블의 열입니다.
   • 조회 열 - 조회할 열입니다.
   • 반환 열 - 값을 검색할 열입니다.
3. 삽입 버튼을 클릭합니다.

 

다음 예제에서는 마법사의 작동을 보여 줍니다.

 

표준 Vlookup

조회 열(동물)이 조회 테이블의 맨 왼쪽 열인 경우 정확히 일치를 위한 일반 VLOOKUP 수식이 삽입됩니다.

 

왼쪽의 Vlookup

조회 열(Animal)이 반환 열(Speed)의 오른쪽에 있는 경우 마법사는 Vlookup에 INDEX MATCH 수식을 오른쪽에서 왼쪽으로 삽입합니다.

셀 참조를 영리하게 사용하기 때문에 참조를 업데이트할 필요 없이 수식을 복사하거나 모든 열로 이동할 수 있습니다.

 

반응형

두 테이블 병합 - Excel VLOOKUP에 대한 수식 없는 대안

Excel 파일이 엄청나게 크고 복잡하면 테이블 병합 마법사를 사용해 보십시오.

이 도구는 다음과 같은 방식으로 작동하는 Excel의 VLOOKUP 기능에 대한 시각적이고 스트레스 없는 대안입니다.

1. 기본 테이블을 선택합니다.
2. 조회 테이블을 선택합니다.
3. 하나 또는 여러 개의 공통 열을 고유 식별자로 선택합니다.
4. 업데이트할 열을 지정합니다.
5. 필요에 따라 추가할 열을 선택합니다.
6. 테이블 병합 마법사가 처리될 때까지 몇 초 동안 기다립니다... 결과를 즐기십시오 :)