메인 컨텐츠로 가기

Excel VLOOKUP 함수

Excel VLOOKUP 함수 는 테이블의 첫 번째 열 또는 범위를 수직으로 일치시켜 지정된 값을 찾은 다음 같은 행의 다른 열에서 해당 값을 반환하는 데 도움이 되는 강력한 도구입니다. VLOOKUP은 매우 유용하지만 때로는 초보자가 이해하기 어려울 수 있습니다. 이 튜토리얼은 다음을 제공하여 VLOOKUP을 마스터하는 데 도움을 주는 것을 목표로 합니다. 주장에 대한 단계별 설명, 유용한 예 또한 일반적인 오류에 대한 솔루션 VLOOKUP 기능을 사용할 때 발생할 수 있습니다.


관련 동영상


인수에 대한 단계별 설명

위의 스크린샷과 같이 VLOOKUP 기능은 주어진 ID 번호를 기반으로 이메일을 찾는 데 사용됩니다. 이제 각 인수를 단계별로 세분화하여 이 예에서 VLOOKUP을 사용하는 방법에 대해 자세히 설명하겠습니다.

1단계: VLOOKUP 기능 시작

결과를 출력할 셀(이 경우 H6)을 선택한 다음, 다음 내용을 입력하여 VLOOKUP 기능을 시작합니다. 수식 표시 줄.

=VLOOKUP(
2단계: 조회 값 지정

먼저 VLOOKUP 함수에서 조회 값(찾고 있는 값)을 지정합니다. 여기서는 특정 ID 번호 6를 포함하는 셀 G1005을 참조합니다.

=VLOOKUP(G6

주의 사항: 조회 값은 데이터 범위의 첫 번째 열에 있어야 합니다.
3단계: 테이블 배열 지정

그런 다음 찾고 있는 값과 반환할 값을 모두 포함하는 셀 범위를 지정합니다. 이 경우 B6:E12 범위를 선택합니다. 이제 수식이 다음과 같이 나타납니다.

=VLOOKUP(G6,B6:E12

주의 사항: VLOOKUP 함수를 복사하여 동일한 열에서 여러 값을 조회하고 다른 결과를 얻으려면 다음과 같이 달러 기호를 추가하여 절대 참조를 사용해야 합니다.
=VLOOKUP(G6,$B$6:$E$12
4단계: 값을 반환할 열 지정

그런 다음 값을 반환할 열을 지정합니다.

이 예에서는 ID 번호를 기반으로 이메일을 반환해야 하므로 여기에 숫자 4를 입력하여 데이터 범위의 네 번째 열에서 값을 반환하도록 VLOOKUP에 지시합니다.

=VLOOKUP(G6,B6:E12,4

5단계: 근사치 또는 정확히 일치하는 항목 찾기

마지막으로 대략적인 일치 또는 정확한 일치를 찾고 있는지 확인합니다.

  • 찾기 위해 정확히 일치, 당신은 그릇된 마지막 인수로.
  • 찾기 위해 대략적인 일치사용 TRUE 마지막 인수로 사용하거나 그냥 공백으로 두십시오.

이 예에서는 정확한 일치를 위해 FALSE를 사용합니다. 이제 공식은 다음과 같습니다.

=VLOOKUP(G6,B6:E12,4,FALSE

Enter 키를 눌러 결과를 얻습니다.

위의 예에서 각 인수를 하나씩 설명함으로써 VLOOKUP 함수의 구문 및 인수를 훨씬 쉽게 이해할 수 있습니다.


구문 및 인수

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

  • 조회 _ 값 (필수): 찾고 있는 값(실수 값 또는 셀 참조)입니다. 이 값은 table_array의 첫 번째 열에 있어야 합니다.
  • 테이블_배열 (필수): 셀 범위에는 조회 값 열과 반환 값 열이 모두 포함됩니다.
  • Col_index (필수): 정수는 반환 값을 포함하는 열 번호를 나타냅니다. table_array의 맨 왼쪽 열에 대해 숫자 1부터 시작합니다.
  • 범위_조회 (선택 사항): VLOOKUP에서 대략적으로 일치하는 항목을 찾을지 정확히 일치하는 항목을 찾을지 결정하는 논리 값입니다.
    • 대략적인 일치 - 이 인수를 다음으로 설정 TRUE, 1 아니면 놔둬 공백.
      중대한: 대략적인 일치를 찾으려면 VLOOKUP이 잘못된 결과를 반환하는 경우 table_array의 첫 번째 열에 있는 값을 오름차순으로 정렬해야 합니다.
    • 정확히 일치 - 이 인수를 다음으로 설정 그릇된 or 0.

이 섹션에서는 VLOOKUP 함수를 보다 포괄적으로 이해하는 데 도움이 되는 몇 가지 예를 보여줍니다.

예 1: VLOOKUP에서 정확한 일치와 대략적인 일치 비교

VLOOKUP을 사용할 때 정확한 일치와 대략적인 일치를 혼동하는 경우 이 섹션을 통해 이러한 혼란을 해결할 수 있습니다.

VLOOKUP의 완전 일치

이 예에서는 E6:E8 범위에 나열된 점수를 기반으로 해당 이름을 찾을 것이므로 셀 F6에 다음 수식을 입력하고 자동 채우기 핸들을 F8로 드래그합니다. 이 수식에서 마지막 인수는 다음과 같이 지정됩니다. 그릇된 정확한 일치 조회를 수행합니다.

=VLOOKUP(E6,$B$6:$C$12,2,FALSE)

그러나 데이터 범위의 첫 번째 열에 점수 98이 없으므로 VLOOKUP에서 #N/A 오류 결과를 반환합니다.

주의 사항: 여기서는 VLOOKUP 함수에서 테이블 배열($B$6:$C$12)을 잠그고 일관된 여러 조회 값에 대한 데이터 집합입니다.
VLOOKUP에서 대략적인 일치

여전히 위의 예를 사용하여 마지막 인수를 다음으로 변경하면 TRUE, VLOOKUP은 대략적인 일치 조회를 수행합니다. 일치하는 항목이 없으면 조회 값보다 작은 다음으로 큰 값을 찾아 해당 결과를 반환합니다.

=VLOOKUP(E6,$B$6:$C$12,2,TRUE)

점수 98이 존재하지 않으므로 VLOOKUP은 98보다 작은 다음으로 큰 값인 95를 찾아 점수 95의 이름을 가장 가까운 결과로 반환합니다.

노트:
  • 이 대략적인 일치 사례에서 table_array의 첫 번째 열에 있는 값은 오름차순으로 정렬되어야 합니다. 그렇지 않으면 VLOOKUP이 올바른 값을 반환하지 않을 수 있습니다.
  • 여기서는 여러 조회 값에 대해 일관된 데이터 집합을 빠르게 참조하기 위해 VLOOKUP 함수에서 테이블 배열($B$6:$C$12)을 잠갔습니다.

예 2: 여러 기준으로 VLOOKUP 사용

이 섹션에서는 Excel에서 여러 조건으로 VLOOKUP을 사용하는 방법을 보여줍니다. 아래 스크린샷과 같이 제공된 이름(H5 셀)과 부서(H6 셀)를 기반으로 급여를 찾으려는 경우 아래 단계를 수행하십시오.

1단계: 도우미 열을 추가하여 조회 열의 값을 연결합니다.

이 경우 도우미 열을 만들어서 값을 연결해야 합니다. 성함 열과 학과 열입니다.

  1. 데이터 범위 왼쪽에 도우미 열을 추가하고 이 열에 머리글을 지정합니다. 스크린샷 보기:
  2. 이 도우미 열에서 머리글 아래의 첫 번째 셀을 선택하고 다음 수식을 입력하십시오. 수식 입력 줄, 그리고 언론 엔터 버튼.
    =C6&" "&D6
    노트: 이 수식에서는 앰퍼샌드(&)를 사용하여 두 열의 텍스트를 결합하여 단일 텍스트를 생성합니다.
    • C6 의 이름입니다. 성함 결합할 열, D6 의 첫 번째 부서입니다. 학과 결합할 열.
    • 이 두 셀의 값은 사이에 공백으로 연결됩니다.
  3. 이 결과 셀을 선택한 다음 자동 완성 핸들 이 수식을 동일한 열의 다른 셀에 적용하려면 아래로.
2단계: 주어진 기준으로 VLOOKUP 함수 적용

결과를 출력할 셀을 선택하고(여기서는 I7을 선택함) 다음 수식을 입력합니다. 수식 입력 줄, 다음을 누릅니다. 엔터 버튼.

=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)
결과

노트:
  • 도우미 열은 데이터 범위의 첫 번째 열로 사용해야 합니다.
  • 이제 급여 열은 데이터 범위의 다섯 번째 열이므로 숫자를 사용합니다. 5 수식의 열 인덱스로.
  • 우리는 기준에 합류해야합니다 I5 또한 I6 (I5& " "&I6) 도우미 열과 동일한 방식으로 연결된 값을 lookup_value 수식의 인수.
  • 두 조건을 lookup_value 인수에 직접 넣고 공백으로 구분할 수도 있습니다(조건이 텍스트인 경우 큰따옴표로 묶는 것을 잊지 마십시오).
    =VLOOKUP("Albee IT",B6:F12,5,FALSE)
  • 더 나은 대안 - 몇 초 만에 여러 기준으로 조회
    다중 조건 조회 특징 Excel 용 Kutools 몇 초 만에 여러 기준으로 쉽게 조회할 수 있습니다. 지금 30일 동안 완전한 기능을 갖춘 무료 평가판을 받으세요!

일반적인 VLOOKUP 오류 및 솔루션

이 섹션에서는 VLOOKUP을 사용할 때 발생할 수 있는 일반적인 오류를 나열하고 해결 방법을 제공합니다.

  일반적인 VLOOKUP 오류 개요:
          
         이유 1: 조회 값이 첫 번째 열에 없습니다.  
     이유 2: 조회 값을 찾을 수 없습니다.  
  ------  이유 3: 조회 값이 가장 작은 값보다 작습니다.  
     이유 4: 숫자가 텍스트로 서식 지정됨  
       이유 5: Table_array가 일정하지 않음  
         
  ------  이유 1: 조회 값이 255자를 초과합니다.  
   이유 2: Col_index가 1보다 작음  
         
  ------  이유 1: Col_index가 열 수보다 큽니다.  
   
         
  ------  이유 1: 조회 열이 오름차순으로 정렬되지 않습니다.  
   이유 2: 열이 삽입 또는 제거됨  
         

#N/A 오류가 반환됨

VLOOKUP에서 가장 일반적인 오류는 #N/A 오류입니다. 이는 Excel에서 찾고 있는 값을 찾을 수 없음을 의미합니다. 다음은 VLOOKUP이 #N/A 오류를 반환할 수 있는 몇 가지 이유입니다.

이유 1: 조회 값이 table_array의 첫 번째 열에 없습니다.

Excel VLOOKUP의 한계 중 하나는 왼쪽에서 오른쪽으로만 볼 수 있다는 것입니다. 따라서 조회 값은 table_array의 첫 번째 열에 있어야 합니다.

아래 스크린샷과 같이 주어진 직책을 기반으로 이름을 반환하고 싶습니다. 여기서 조회 값(영업 관리자)가 table_array의 두 번째 열에 있고 반환 값이 조회 열의 왼쪽에 있으므로 VLOOKUP에서 #N/A 오류를 반환합니다.

솔루션

다음 솔루션 중 하나를 적용하여 이 오류를 수정할 수 있습니다.

  • 열 재정렬
    열을 재정렬하여 table_array의 첫 번째 열에 조회 열을 배치할 수 있습니다.
  • INDEX 및 MATCH 함수를 함께 사용
    여기서는 이 문제를 해결하기 위해 VLOOKUP의 대안으로 INDEX 및 MATCH 함수를 함께 사용합니다.
    =INDEX(B6:B12,MATCH(F6,C6:C12,0))
  • XLOOKUP 기능 사용(Excel 365, Excel 2021 이상 버전에서 사용 가능)
    =XLOOKUP(F6,C6:C12,B6:B12)

이유 2: 조회 열에서 조회 값을 찾을 수 없습니다(완전 일치).

VLOOKUP에서 #N/A 오류를 반환하는 가장 일반적인 이유 중 하나는 찾고 있는 값을 찾을 수 없기 때문입니다.

아래 예와 같이 E98에서 주어진 점수 6을 기준으로 이름을 찾을 것입니다. 그러나이 점수는 데이터 범위의 첫 번째 열에 없으므로 VLOOKUP은 #N/A 오류 결과를 반환합니다.

솔루션

이 오류를 수정하려면 다음 해결 방법 중 하나를 시도해 보십시오.

  • VLOOKUP에서 조회 값보다 작은 다음으로 큰 값을 검색하려면 마지막 인수를 변경하세요. 그릇된 (정확히 일치) TRUE (대략적인 일치). 자세한 내용은 다음을 참조하십시오. 예 1: VLOOKUP을 사용한 정확한 일치와 대략적인 일치 비교.
  • 마지막 인수를 변경하지 않고 조회 값을 찾을 수 없는 경우 미리 알림을 받으려면 IFERROR 함수 내에 VLOOKUP 함수를 포함할 수 있습니다.
    =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

이유 3: 조회 값이 조회 열의 가장 작은 값보다 작습니다(대략 일치).

아래 스크린샷과 같이 대략적인 일치 조회를 수행하고 있습니다. 찾고 있는 값(이 경우 ID 번호 1001)이 조회 열의 가장 작은 값 1002보다 작으므로 VLOOKUP에서 #N/A 오류를 반환합니다.

솔루션

다음은 두 가지 솔루션입니다.

  • 조회 값이 조회 열의 가장 작은 값보다 크거나 같은지 확인하십시오.
  • Excel에서 조회 값을 찾을 수 없음을 알리도록 하려면 다음과 같이 VLOOKUP 함수를 IFERROR 함수에 중첩하면 됩니다.
    =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

이유 4: 숫자가 텍스트로 서식 지정됨

아래 스크린샷에서 볼 수 있듯이 이 예제의 #N/A 오류 결과는 원본 테이블의 조회 셀(G6)과 조회 열(B6:B12) 간의 데이터 유형 불일치로 인해 발생합니다. 여기서 G6의 값은 숫자이고 B6:B12 범위의 값은 텍스트 형식의 숫자입니다.

: 숫자가 텍스트로 변환되면 셀의 왼쪽 상단에 작은 녹색 삼각형이 표시됩니다.

솔루션

이 문제를 해결하려면 조회 값을 다시 숫자로 변환해야 합니다. 다음은 두 가지 방법입니다.

  • 숫자로 변환 기능 적용
    텍스트를 숫자로 변환하려는 셀을 클릭하고 이 버튼을 선택합니다.  셀 옆에서 다음을 선택합니다. 숫자로 변환.
  • 편리한 도구를 적용하여 텍스트와 숫자 간 일괄 변환
    텍스트와 숫자 간 변환 특징 Excel 용 Kutools 셀 범위를 텍스트에서 숫자로 또는 그 반대로 쉽게 변환할 수 있습니다. 지금 30일 동안 완전한 기능을 갖춘 무료 평가판을 받으세요!

이유 5: VLOOKUP 수식을 다른 셀로 끌 때 table_array가 일정하지 않습니다.

아래 스크린샷과 같이 E6과 E7에는 두 개의 조회 값이 있습니다. F6에서 첫 번째 결과를 얻은 후 셀 F6에서 F7로 VLOOKUP 수식을 드래그하면 #N/A 오류 결과가 반환됩니다. 이는 셀 참조(B6:C12)가 기본적으로 상대적이고 행을 따라 아래로 이동할 때 조정되기 때문입니다. 테이블 배열이 더 이상 조회 점수 7을 포함하지 않는 B13:C73으로 이동되었습니다.

솔루션

다음을 추가하여 일정하게 유지하려면 테이블 배열을 잠글 필요가 있습니다. $ 셀 참조의 행과 열 앞에 부호를 붙입니다. Excel의 절대 참조에 대해 자세히 알아보려면 다음 자습서를 살펴보십시오. 엑셀 절대참조(만드는법과 활용법).

반환되는 #VALUE 오류

다음 조건으로 인해 VLOOKUP이 #VALUE 오류 결과를 반환할 수 있습니다.

이유 1: 조회 값이 255자를 초과합니다.

아래 스크린샷과 같이 셀 H4의 조회 값이 255자를 초과하므로 VLOOKUP에서 #VALUE 오류 결과를 반환합니다.

솔루션

이 제한을 해결하기 위해 더 긴 문자열을 처리할 수 있는 다른 조회 함수를 적용할 수 있습니다. 다음 수식 중 하나를 시도하십시오.

  • 색인 및 일치:
    =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))
  • XLOOKUP 함수 (Excel 365, Excel 2021 및 이후 버전에서 사용 가능):
    =XLOOKUP(H4,B5:B11,E5:E11)

이유 2: col_index 인수가 1보다 작음

열 인덱스는 반환하려는 값이 포함된 테이블 배열의 열 번호를 지정합니다. 이 인수는 테이블 배열의 유효한 열에 해당하는 양수여야 합니다.

1보다 작은 열 인덱스(예: XNUMX 또는 음수)를 입력하면 VLOOKUP이 테이블 배열에서 해당 열을 찾을 수 없습니다.

솔루션

이 문제를 해결하려면 VLOOKUP 수식의 열 인덱스 인수가 테이블 배열의 유효한 열에 해당하는 양수인지 확인하세요.

반환되는 #REF 오류

이 섹션에서는 VLOOKUP이 #REF 오류를 반환하는 한 가지 이유를 나열하고 이 문제에 대한 해결책을 제공합니다.

이유: col_index 인수가 열 수보다 큽니다.

아래 스크린샷에서 볼 수 있듯이 테이블 배열에는 열이 4개뿐입니다. 그러나 VLOOKUP 수식에 지정한 열 인덱스는 테이블 배열의 열 수보다 큰 5입니다. 결과적으로 VLOOKUP은 열을 찾을 수 없으며 궁극적으로 #REF 오류를 반환합니다.

솔루션

  • 올바른 열 번호 지정
    VLOOKUP 수식의 열 인덱스 인수가 테이블 배열의 유효한 열에 해당하는 숫자인지 확인하세요.
  • 지정된 열 헤더를 기반으로 열 번호를 자동으로 가져옵니다.
    테이블에 많은 열이 포함된 경우 올바른 열 인덱스 번호를 결정하는 데 문제가 있을 수 있습니다. 여기에서 VLOOKUP 함수에 MATCH 함수를 중첩하여 인증서 열 헤더를 기반으로 열의 위치를 ​​찾을 수 있습니다.
    =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)
    주의 사항: 위 식에서 MATCH("이메일",B5:E5, 0) 함수는 "의 열 번호를 가져오는 데 사용됩니다.이메일" 날짜 범위 B6:E12의 열. 여기서 결과는 4이며 VLOOKUP 함수에서 col_index로 사용됩니다.

잘못된 값이 반환됨

VLOOKUP이 올바른 결과를 반환하지 않는 경우 다음과 같은 이유 때문일 수 있습니다.

이유 1: 조회 열이 오름차순으로 정렬되지 않습니다.

마지막 인수를 다음으로 설정한 경우 TRUE (또는 비워 두었다) 근사 일치의 경우 조회 열이 오름차순으로 정렬되지 않으면 결과 값이 올바르지 않을 수 있습니다.

솔루션

조회 열을 오름차순으로 정렬하면 이 문제를 해결하는 데 도움이 됩니다. 이렇게 하려면 다음 단계를 따르십시오.

  1. 조회 열에서 데이터 셀을 선택하고 Data 탭에서 작은 것에서 큰 것까지 정렬 에서 정렬 및 필터링 그룹입니다.
  2. . 정렬 경고 대화 상자에서 선택 확장 옵션을 클릭하고 OK.

이유 2: 열이 삽입 또는 제거됨

아래 스크린샷과 같이 원래 반환하고자 했던 값이 테이블 배열의 4번째 열에 있으므로 col_index 번호를 XNUMX로 지정합니다. 새로운 열이 삽입됨에 따라 결과 열은 테이블의 XNUMX번째 열이 됩니다. 배열로 인해 VLOOKUP이 잘못된 열의 결과를 반환합니다.

솔루션

다음은 두 가지 솔루션입니다.

  • 반환 열의 위치와 일치하도록 열 인덱스 번호를 수동으로 변경할 수 있습니다. 여기서 공식은 다음과 같이 변경되어야 합니다.
    =VLOOKUP(H6,B6:F12,5,FALSE)
  • 이 예제의 이메일 열과 같은 인증서 열에서 항상 결과를 반환하려는 경우. 다음 수식은 열이 테이블 배열에서 삽입되거나 제거되는지 여부에 관계없이 지정된 열 머리글을 기반으로 열 인덱스를 자동으로 일치시키는 데 도움이 될 수 있습니다.
    =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

기타 기능 참고 사항

  • VLOOKUP은 왼쪽에서 오른쪽으로만 값을 찾습니다.
    조회 값은 맨 왼쪽 열에 있고 결과 값은 조회 열 오른쪽에 있는 열에 있어야 합니다.
  • 마지막 인수를 비워 두면 VLOOKUP은 기본적으로 대략적인 일치를 사용합니다.
  • VLOOKUP은 대소문자를 구분하지 않는 조회를 수행합니다.
  • 일치 항목이 여러 개인 경우 VLOOKUP은 테이블 배열의 행 순서에 따라 테이블 배열에서 찾은 첫 번째 일치 항목만 반환합니다.

최고의 사무 생산성 도구

인기 기능: 중복 항목 찾기, 강조 표시 또는 식별   |  빈 행 삭제   |  데이터 손실 없이 열이나 셀 결합   |   수식없이 반올림 ...
슈퍼 조회: 다중 기준 VLookup    다중 값 VLookup  |   여러 시트에 걸친 VLookup   |   퍼지 조회 ....
고급 드롭다운 목록: 드롭다운 목록을 빠르게 생성   |  종속 드롭다운 목록   |  다중 선택 드롭 다운 목록 ....
열 관리자: 특정 개수의 열 추가  |  열 이동  |  Toggle 숨겨진 열의 가시성 상태  |  범위 및 열 비교 ...
특색 지어진 특징: 그리드 포커스   |  디자인보기   |   큰 수식 바    통합 문서 및 시트 관리자   |  리소스 라이브러리 (자동 텍스트)   |  날짜 선택기   |  워크 시트 결합   |  셀 암호화/해독    목록으로 이메일 보내기   |  슈퍼 필터   |   특수 필터 (굵게/기울임꼴/취소선 필터링...) ...
상위 15개 도구 세트12 본문 도구 (텍스트 추가, 문자 제거,...)   |   50+ 거래차트 유형 (Gantt 차트,...)   |   40+ 실용 방식 (생일을 기준으로 나이 계산,...)   |   19 삽입 도구 (QR 코드 삽입, 경로에서 그림 삽입,...)   |   12 매출 상승 도구 (숫자를 단어로, 환율,...)   |   7 병합 및 분할 도구 (고급 결합 행, 셀 분할,...)   |   ... 그리고 더

Excel용 Kutools로 Excel 기술을 강화하고 이전과는 전혀 다른 효율성을 경험해 보세요. Excel용 Kutools는 생산성을 높이고 시간을 절약하기 위해 300개 이상의 고급 기능을 제공합니다.  가장 필요한 기능을 얻으려면 여기를 클릭하십시오...

kte 탭 201905


Office Tab은 Office에 탭 인터페이스를 제공하여 작업을 훨씬 쉽게 만듭니다.

  • Word, Excel, PowerPoint에서 탭 편집 및 읽기 사용, Publisher, Access, Visio 및 Project.
  • 새 창이 아닌 동일한 창의 새 탭에서 여러 문서를 열고 만듭니다.
  • 생산성을 50% 높이고 매일 수백 번의 마우스 클릭을 줄입니다!
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
thank you so much
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations