VLOOKUP 함수 사용법 및 구체적인 예

Excel의 VLOOKUP 기능은 데이터를 조회하는 데 매우 유용합니다. Excel에서 VLOOKUP 함수를 사용하는 방법 에 대해 알아야 할 사항은 다음과 같습니다 .

Excel 스프레드시트에서 특정 값을 확인하고 이에 대한 정보를 조회하고 싶습니다. 이 경우 VLOOKUP을 실행하면 상당한 노력이 절약됩니다. 이는 Microsoft Excel에서 수직 쿼리를 실행하는 가장 좋은 방법 중 하나입니다. 이 기사에서는 Microsoft Excel의 VLOOKUP 기능의 의미, 사용법 및 예시를 설명합니다.

• 이해해야 할 Excel의 가장 기본적인 기능입니다.

VLOOKUP 기능을 사용하는 방법

• VLOOKUP 함수란 무엇입니까?
• Excel 2016, 2013, 2010, 2007, 2003의 VLOOKUP 함수 공식
• VLOOKUP 기능 사용에 대한 비디오 자습서
• 예 1: VLOOKUP 함수를 사용하여 학생과 교직원의 평가를 평가합니다.
• 예 2: 절대 감지에 VLOOKUP을 사용하여 데이터 가져오기
• 예 3: VLOOKUP을 사용하여 데이터 추출
• 예 4: 2개의 서로 다른 Excel 시트에서 VLOOKUP 함수 사용
• VLOOKUP 함수는 무엇에 사용하나요?
• VLOOKUP 함수에 대해 기억해야 할 사항

VLOOKUP 함수란 무엇입니까?

VLOOKUP 함수는 Excel의 데이터 검색 기능입니다. 여러분은 Excel에 대한 배경 지식이 있고 SUM, AVERAGE, TODAY와 같은 기본 기능을 사용할 수 있다고 가정합니다. VLOOKUP의 가장 일반적인 기능 중 하나는 데이터 기능입니다. 즉, 데이터베이스 테이블 또는 더 간단하게는 항목 목록에서 작동합니다. 목록은 매우 다양합니다. 회사의 직원, 제품 범주, 고객 목록 등에 대한 테이블을 만들 수 있습니다. 아래는 A회사가 시장에 판매하고 있는 제품 목록입니다.

데이터베이스 테이블에는 각 제품에 대한 식별자가 있는 경우가 많습니다. 위의 데이터 테이블에서 특수 식별 기호는 "품목 코드" 열입니다. 참고: VLOOKUP 함수를 사용하려면 데이터 테이블에 코드나 ID와 같은 식별 기호가 포함된 열이 있어야 하며 위 표와 같이 첫 번째 열이어야 합니다.

VLOOKUP은 아마도 Excel에서 가장 유명한 함수일 것입니다. 그러나 좋은 이유와 나쁜 이유가 모두 있습니다. 좋은 점은 VLOOKUP 기능이 사용하기 쉽고 매우 유용한 기능을 제공한다는 것입니다. 특히 신규 사용자의 경우 VLOOKUP 기능이 테이블을 스캔하고 일치하는 항목을 찾아 정확한 결과를 반환하는 것을 보는 것은 매우 흥미로운 일입니다. VLOOKUP 기능을 능숙하게 사용하는 것은 초보자부터 능숙한 Excel 사용자까지 필수적인 기술입니다.

부정적인 측면에서는 VLOOKUP 기능이 제한되어 있으며 위험한 기본값을 가지고 있습니다. INDEX 및 MATCH(또는 XLOOKUP)와 달리 VLOOKUP 함수에는 첫 번째 열에 조회 값이 있는 완전한 테이블이 필요합니다. 이로 인해 여러 기준으로 VLOOKUP 함수를 사용하기가 어렵습니다. 또한 VLOOKUP의 기본 일치 동작을 사용하면 잘못된 결과를 쉽게 얻을 수 있습니다. 하지만 걱정하지 마! VLOOKUP 기능을 성공적으로 사용하려면 기본 사항을 익히는 것이 중요합니다.

VLOOKUP에는 lookup_value, table_array, column_index_num 및 range_lookup이라는 4개의 인수가 있습니다. Lookup_value는 찾을 값이고 table_array는 검색할 수직 데이터 범위입니다. table_array의 첫 번째 열에는 검색할 조회 값이 포함되어야 합니다. column_index_num 인수는 검색할 값의 열 번호입니다. 여기서 table_array의 첫 번째 열은 열 1입니다.

마지막으로 range_lookup은 일치 항목을 찾는 동작을 제어합니다. range_lookup이 TRUE이면 VLOOKUP 함수는 대략적인 일치를 수행합니다. range_lookup이 FALSE이면 VLOOKUP 함수는 정확한 일치를 수행합니다. 중요한 것은 range_lookup은 선택 사항이고 기본값은 TRUE이므로 VLOOKUP은 기본적으로 대략적인 일치를 수행합니다.

Excel의 VLOOKUP 함수 공식

Excel의 VLOOKUP 함수에는 다음 수식이 있습니다.

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

거기에:

• VLOOKUP: 함수 이름입니다.
• 굵게 표시된 매개변수 는 필수입니다.
• lookup_value: 조회에 사용되는 값
• table_array: 검색할 값이 포함된 테이블입니다. 앞에 $ 기호를 붙여 절대값을 입력합니다(예: $A$3:$E$40).
• col_index_num: table_array에 대한 조회 값이 포함된 열의 순서입니다. 예를 들어 $A$3:$E$40 테이블에서 B 열에는 검색할 값이 포함되어 있으며 여기서 col_index_num은 2가 됩니다. $C$3:$F$40 테이블, E열에 조회 값이 포함되어 있고 여기서 col_index_num은 3입니다.
• range_lookup: 검색 범위이며, TRUE는 1(상대 조회), FALSE는 0(절대 조회)과 같습니다. 이 매개변수는 공식에 항상 필요한 것은 아닙니다.

상대 검색은 table_array에서 검색할 값을 순서대로(오름차순, 내림차순 또는 알파벳순) 정렬한 경우에만 적용할 수 있습니다. 이러한 테이블을 사용하면 무한 IF 함수를 사용하는 것과 유사한 상대 조회를 사용할 수 있습니다 . 검색이 불가능하거나 정렬이 되지 않는 값은 절대검색을 이용하여 정확한 값을 찾아보세요.

VLOOKUP 기능 사용에 대한 비디오 자습서

예 1: 학생과 교직원의 평가를 평가하는 VLOOKUP 함수

다음과 같은 학생 성적표가 있다고 가정해 보겠습니다.

그리고 순위 규정표는 다음과 같습니다.

이제 VLOOKUP 함수를 사용하여 학생 평가를 입력하겠습니다. 순위표가 낮은 것부터 높은 것(약한 것부터 좋은 것 순)으로 정렬되어 있으므로 이 경우에는 상대 검색을 사용할 수 있습니다.

Excel에서는 이 2개의 테이블이 다음과 같이 표시됩니다.

여기서 감지할 값은 6행부터 C열에 있습니다. 검색 범위는 $A$18:$B$21이고, 감지된 값이 포함된 열의 순서는 2입니다.

D6 셀에 =VLOOKUP($C6,$A$18:$B$21,2,1) 수식을 입력합니다. 이것은 상대 검색 공식입니다. 원하는 경우(또는 순위가 정렬되지 않았기 때문에) 다음과 같이 공식에 0을 추가하여 절대 검색을 수행할 수 있습니다. =VLOOKUP($C6 ,$A$18:$B$21,2, 0). 엔터 키를 치시오.

D6 셀을 클릭하면 오른쪽 하단에 작은 사각형이 나타납니다. 이를 클릭하고 테이블 아래로 끌어 나머지 학생의 채점 공식을 복사합니다.

당시 VLOOKUP 기능을 이용하여 학생들의 학업 성취도를 분류한 결과는 다음과 같습니다.

C6 앞에 $를 사용해야 하는 이유가 궁금하십니까? $는 열 C를 고정한 상태로 유지하고 수식을 테이블 아래로 끌 때만 행을 변경합니다. 또한 $A$18:$B$21을 사용하면 순위 규칙 표를 수정하여 수식을 끌어도 순위가 변경되지 않도록 할 수 있습니다.

예 2: VLOOKUP 함수는 절대적으로 검색하여 데이터를 가져옵니다.

직원 코드, 성명, 직위를 저장하는 직원 데이터 테이블이 있다고 가정해 보겠습니다. 또 다른 테이블에는 직원 코드, 고향, 교육 수준이 저장됩니다. 이제 각 직원의 고향 정보와 교육 수준을 입력하려면 어떻게 해야 할까요?

다음과 같이 직원 및 직원 고향 테이블이 있다고 가정합니다.

이제 직원의 고향 정보를 입력하려고 합니다. D4 셀에 다음과 같이 절대 검색 수식을 입력합니다. =Vlookup($A4,$A$16:$C$25,2,0)

그런 다음 Enter 키를 누릅니다. D4 셀 모서리에 나타나는 작은 사각형을 클릭하고 테이블을 가로질러 아래로 끌어서 다른 직원에게 수식을 복사합니다.

직원의 자격 정보를 입력하려면 E4 셀에 절대 검색 공식 =VLOOKUP($A4,$A$16:$C$25,3,0)을 입력합니다.

계속해서 Enter 키를 누르고 아래로 스크롤하여 나머지 직원에게 수식을 복사하면 다음과 같은 결과를 얻게 됩니다.

예 3: VLOOKUP을 사용하여 데이터 추출

예제 2의 데이터 세트를 계속 사용하면 이제 직원 3명의 고향인 Nguyen Hoang Anh, Tran Van Anh 및 Nguyen Quang Vinh을 찾을 수 있습니다. 새 테이블 F15:G18에 추출했습니다.

내 고향과 자격을 입력한 후 테이블 A3:E13에서 이 검색을 수행하겠습니다. 이제 G16 셀에 다음 절대 검색 수식을 입력합니다. =VLOOKUP($F16,$B$3:$E$13,3,0) > Enter 키를 누릅니다.

나머지 2명의 직원에 대한 수식을 복사하면 다음과 같은 결과를 얻습니다.

이 예에서는 감지 값이 B열에 있으므로 감지 영역은 A열이 아닌 B열에서 계산됩니다.

예 4: 2개의 서로 다른 Excel 시트에서 VLOOKUP 함수 사용

예제 2의 데이터 세트로 돌아가서 직원의 자격과 고향을 입력한 후 시트 이름을 QTM으로 지정합니다.

QTM1이라는 스프레드시트의 다른 시트에서 직원의 배열 순서가 변경된 직원의 자격 및 직위에 대한 정보를 가져와야 합니다. VLOOKUP 기능의 진정한 힘을 볼 때입니다.

직원의 "자격"에 대한 데이터를 찾으려면 QTM1 시트의 C4 셀에 =VLOOKUP($B4,QTM!$B$3:$E$13,4,0) 수식을 입력합니다.

거기에:

• B4는 탐지에 사용된 값이 포함된 열입니다.
• QTM! 는 감지할 값이 포함된 테이블이 포함된 시트의 이름입니다. 시트 이름 뒤에 !
• $B$3:$E$13은 조회값이 포함된 테이블과 해당 테이블(QTM)이 포함된 시트입니다.
• 4는 QTM 시트의 "성명" 열에서 계산된 "자격" 열의 일련번호입니다.
• 0은 절대 감지입니다.

Enter 키를 누른 다음 테이블에 남아 있는 모든 직원에 대한 수식을 복사하면 다음과 같은 결과가 나타납니다.

직원의 "직위" 데이터를 찾으려면 QTM1 시트의 D4 셀에 =VLOOKUP($B4,QTM!$B$3:$E$13,2,0) 수식을 입력하고 Enter 키를 누릅니다.

나머지 직원에 대한 수식을 복사하면 다음과 같은 결과를 얻습니다.

VLOOKUP 함수는 무엇에 사용하나요?

먼저 "VLOOKUP 기능을 무엇에 사용합니까?"라는 질문에 대한 정확한 답을 찾아야 합니다.

VLOOKUP 함수는 사용 가능한 식별자를 기반으로 데이터 필드 또는 목록에서 정보 조회를 지원하는 데 사용됩니다.

예를 들어 제품 코드와 함께 VLOOKUP 함수를 다른 스프레드시트에 삽입하면 해당 코드에 해당하는 제품 정보가 표시됩니다. 해당 정보는 작성하는 레시피의 내용에 따라 설명, 가격, 재고 수량일 수 있습니다.

찾아야 하는 정보의 양이 적을수록 VLOOKUP 함수를 작성하기가 더 어려워집니다. 일반적으로 이 함수는 재사용 가능한 워크시트에서 템플릿으로 사용됩니다. 적절한 제품 코드를 입력할 때마다 시스템은 해당 제품에 대해 필요한 모든 정보를 검색합니다.

VLOOKUP 함수에 대해 기억해야 할 사항

다음은 Excel의 VLOOKUP 함수에 대해 기억해야 할 중요한 사항 목록입니다.

• range_lookup을 생략하면 VLOOKUP은 정확하지 않은 일치를 허용하지만 가능한 경우 정확한 일치를 계속 사용합니다.
• 이 함수의 가장 큰 제한은 항상 오른쪽의 값을 취한다는 것입니다. 이 함수는 테이블의 첫 번째 열 오른쪽에 있는 열에서 데이터를 검색합니다.
• 조회 열에 중복된 값이 포함된 경우 VLOOKUP 함수는 첫 번째 값만 일치시킵니다.
• 이 함수는 대소문자를 구분하지 않습니다.
• 워크시트에 기존 VLOOKUP 수식이 있다고 가정합니다. 이 경우 테이블에 열을 삽입하면 수식이 깨질 수 있습니다. 열 인덱스 값이 하드 코딩되어 열을 삽입하거나 삭제할 때 자동으로 변경되지 않기 때문입니다.
• VLOOKUP에서는 별표(*) 또는 물음표(?)와 같은 와일드카드 문자를 사용할 수 있습니다.
• 테이블에서 텍스트로 입력된 숫자가 포함된 함수로 작업한다고 가정해 보겠습니다. 테이블의 열에서 숫자를 텍스트로 가져오는 경우에는 문제가 되지 않습니다. 그러나 테이블의 첫 번째 열에 텍스트로 입력된 숫자가 포함되어 있으면 #N/A! 조회 값도 텍스트가 아닌 경우 표시됩니다.
• 오류 #N/A! VLOOKUP 함수가 제공된 조회_값과 일치하는 항목을 찾지 못하는 경우 발생합니다.
• 오류 #REF! 다음과 같은 경우에 발생합니다.
  • col_index_num 인수가 제공된 table_array의 열 수보다 큽니다.
  • 수식이 존재하지 않는 셀을 참조하려고 했습니다.
• 오류 #VALUE! 다음과 같은 경우에 발생합니다.
  • col_index_num 인수가 1보다 작거나 숫자 값으로 인식되지 않습니다.
  • range_lookup 인수가 논리값 TRUE 또는 FALSE 중 하나로 인식되지 않습니다.

Excel에서 VLOOKUP과 XLOOKUP 함수의 주요 차이점

그 - 그래

아니요 - 아니요

더보기:

• Excel에서 IF 함수를 사용하는 방법
• Excel에서 AVERAGEIF 함수를 사용하는 방법

VLookup 사용 방법을 알고 나면 일반적인 오류와 문제 해결 방법도 알아야 합니다.

Excel에서 VLOOKUP 함수를 사용할 때 발생하는 일반적인 오류

조회 값이 잘못된 열에 있습니다.

VLOOKUP 수식을 사용할 때 발생하는 가장 일반적인 오류 중 하나는 함수가 #N/A 값만 반환한다는 것입니다. 이 오류는 VLOOKUP에서 검색하도록 요청한 조회 값을 찾을 수 없을 때 발생합니다.

경우에 따라 VLOOKUP 대신 XLOOKUP 함수를 사용할 수 있지만 이 오류는 VLOOKUP 함수에서만 쉽게 수정할 수 있습니다.

문제를 해결하는 방법:

VLOOKUP이 원하는 값을 찾지 못한 이유는 해당 값이 테이블에 없기 때문일 수 있습니다. 그러나 #N/A 값을 모두 반환하는 경우 조회 값은 텍스트이고 왼쪽에서 오른쪽으로만 읽을 수 있기 때문에 그럴 가능성이 높습니다.

Excel에서 VLOOKUP용 테이블을 만들 때 반환하려는 값의 왼쪽에 조회 값을 배치해야 합니다. 이를 수행하는 가장 쉬운 방법은 테이블의 첫 번째 열에 배치하는 것입니다.

이제 수식은 값을 반환합니다.

전반적으로 VLOOKUP은 Microsoft Excel에서 데이터를 더 빠르게 쿼리할 수 있는 좋은 방법입니다. VLOOKUP 쿼리는 열에서 수직으로 실행되고 몇 가지 다른 제한 사항이 있지만 Microsoft는 Excel의 조회 기능을 지속적으로 업데이트하여 적용 가능성을 확장합니다. 예를 들어 HLOOKUP, XLOOKUP...은 다양한 방향으로 데이터를 조회하는 데 도움이 될 수 있습니다. LuckyTemplates.com은 다음 기사에서 이 기능을 사용하는 방법에 대한 정보를 계속 제공할 것입니다.